Bug #109333 query with/without index get different result

• Submitted: 11 Dec 2022 13:13
• Modified: 13 Dec 2022 12:46
• Reporter: x j
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.31
• OS: Any
• 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.