Bug #104325 Prepared statements does not work with multi-value indexes
Submitted: 15 Jul 2021 12:46 Modified: 3 Sep 2021 21:38
Reporter: Vladimir Rudev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S3 (Non-critical)
Version:8.0.25 OS:Linux
Assigned to: CPU Architecture:Any

[15 Jul 2021 12:46] Vladimir Rudev
Description:
MySQL returns empty result when executing prepared statement with multi-value index.
Docker image sha256:52b8406e4c32b8cf0557f1b74517e14c5393aff5cf0384eff62d9e81f4985d4b
8.0.25

How to repeat:
create table Bug(
    id bigint auto_increment primary key,
    data JSON not null,
    index idx(( CAST(data AS UNSIGNED ARRAY)))
);

insert into Bug(data) values ('["123", "456"]');

prepare stmt from 'select * from Bug where ? member of(data)';
set @id=123;
execute stmt using @id;
# empty result set

select * from Bug where 123 member of(data);
+--+--------------+
|id|data          |
+--+--------------+
|1 |["123", "456"]|
+--+--------------+
[16 Jul 2021 7:53] MySQL Verification Team
Hello Vladimir,

Thank you for the report and test case.

regards,
Umesh
[11 Aug 2021 9:36] Knut Anders Hatlen
There are two reasons why the prepared statement gives a different result than the regular statement:

1) The regular statement uses the index, whereas the prepared statement doesn't. This is because of bug#102359.

2) The index does not distinguish between JSON strings and JSON numbers. All values are coerced to unsigned integers when they are stored in the index (since the type of the index is array of unsigned integers). JSON strings and JSON numbers are usually considered not equal. But when the index is used, the string "123" has been stored as the unsigned integer 123 in the index, and then "123" and 123 compare equal.

It has been discussed before whether 2 is a bug, or just an artifact that should be documented. It's certainly unexpected, and it would be preferable if the same result is returned regardless of whether the index is used. The argument for not treating it as a bug has been that the user in this case has explicitly requested that the data is indexed as unsigned integers, and then it's not totally unreasonable that the optimizer makes the assumption that the column only contains unsigned integers, and that it's acceptable with this result difference if the user has inserted data into the column with another type (string) than the one specified in the index (unsigned integer). I don't remember if that discussion ever came to a conclusion.
[23 Aug 2021 14:54] Yubao Liu
You may check this PR: https://github.com/mysql/mysql-server/pull/360
[3 Sep 2021 21:38] Jon Stephens
This is fixed in MySQL 8.0.27 by the fix for BUG#104700. See same for docs info.

Closed.