Bug #109333 query with/without index get different result
Submitted: 11 Dec 2022 13:13 Modified: 13 Dec 2022 12:46
Reporter: x j Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: json;multi-valued index

[11 Dec 2022 13:13] x j
Description:
mysql8.0.31 will produce different result if use multi-valued index.

How to repeat:
mysql> create table t(a int primary key auto_increment, b json, ch char(20), c int, key idx((cast(b as
signed array))));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t (b) values ('["1","2"]');
insert into t (b) values ('[1, 1]');Query OK, 1 row affected (0.01 sec)

mysql> insert into t (b) values ('[1, 1]');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t ignore index(idx) where json_contains(b, '["1"]');
+---+------------+------+------+
| a | b          | ch   | c    |
+---+------------+------+------+
| 1 | ["1", "2"] | NULL | NULL |
+---+------------+------+------+
1 row in set (0.00 sec)

mysql> select * from t force index(idx) where json_contains(b, '["1"]');
+---+------------+------+------+
| a | b          | ch   | c    |
+---+------------+------+------+
| 1 | ["1", "2"] | NULL | NULL |
| 2 | [1, 1]     | NULL | NULL |
+---+------------+------+------+
2 rows in set (0.00 sec)
[12 Dec 2022 9:42] MySQL Verification Team
Hello x j,

Thank you for the report and test case.

regards,
Umesh
[13 Dec 2022 12:46] x j
May I know how you will fix this bug? change the json_contains function to make it return results including `1` or change the multi-valued index, and let it filter out the `1`. I will change my code according to the fix.