MySQL Bugs: #103452: utf8mb4_bin collation not derived by prepared statement, skipping index

Bug #103452	utf8mb4_bin collation not derived by prepared statement, skipping index
Submitted:	23 Apr 2021 12:23	Modified:	27 May 2021 12:52
Reporter:	Jarno van Leeuwen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S2 (Serious)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	collation, INDEX, prepared

Description:
Given a table with a varchar utf8mb4_bin primary key column, executing a prepared select statement does not utilize the index. The problem has started in MySQL 8.0.22 and is also present in 8.0.23.

How to repeat:
Create table:

CREATE TABLE `tmp` (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `other` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Insert lots of data to detect no index is used on selecting:

prepare stmt1 from 'select * from tmp where id = ?';
set @pc = 'id';
execute stmt1 using @pc;

You would expect that the id parameter is using the same collation as the column that is being compared to (utf8mb4_bin), but it seems that the server or table default is being used. This results in no index being utilized.

Hi Mr. Leeuwen,

Thank you for your bug report.

In order to process this report, we need additional informations.

First of all, can you run the statement by itself, without being prepared and check whether index is used or not.

Second, can you send us both EXPLAIN EXTENDED and EXPLAIN ANALYZE so that we can see what is optimizer actually doing.

Next, how many rows are there with a value of 'id' ????

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".