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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.