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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | json;multi-valued index | ||
[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.
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)