| Bug #108640 | multi-valued index with composite index get wrong result | ||
|---|---|---|---|
| Submitted: | 29 Sep 2022 7:05 | Modified: | 29 Sep 2022 8:37 |
| Reporter: | x j | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Sep 2022 8:35]
MySQL Verification Team
Hello x j,
Thank you for the report and test case.
Imho this issue is fixed in 8.0.29+, could you please confirm if you are seeing the issue in latest GA? Thank you.
- 8.0.29+ not affected
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.29 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table t(a int, b char(10), c json, index idx(a, b, (cast(c->'$.a' as unsigned Array))));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t values (1, 'a', '{"a": [1, 2, 3]}'), (2, 'b', '{"a": [4, 5, 6]}');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t force index(idx) where a = 1;
+------+------+------------------+
| a | b | c |
+------+------+------------------+
| 1 | a | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)
mysql> select * from t ignore index(idx) where a = 1;
+------+------+------------------+
| a | b | c |
+------+------+------------------+
| 1 | a | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)
- 8.0.28 affected
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test
Database changed
mysql> create table t(a int, b char(10), c json, index idx(a, b, (cast(c->'$.a' as unsigned Array))));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t values (1, 'a', '{"a": [1, 2, 3]}'), (2, 'b', '{"a": [4, 5, 6]}');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t force index(idx) where a = 1;
+------+------+------------------+
| a | b | c |
+------+------+------------------+
| 1 | a | {"a": [1, 2, 3]} |
| 1 | a | {"a": [1, 2, 3]} |
| 1 | a | {"a": [1, 2, 3]} |
+------+------+------------------+
3 rows in set (0.00 sec)
mysql> select * from t ignore index(idx) where a = 1;
+------+------+------------------+
| a | b | c |
+------+------+------------------+
| 1 | a | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)
regards,
Umesh
[29 Sep 2022 8:37]
x j
great， thinks
Description: if we define a table that contains a composite index that contains a multi-valued index, it will get the wrong result whining using it. How to repeat: create table t(a int, b char(10), c json, index idx(a, b, (cast(c->'$.a' as unsigned Array)))); insert into t values (1, 'a', '{"a": [1, 2, 3]}'), (2, 'b', '{"a": [4, 5, 6]}'); select * from t force index(idx) where a = 1; select * from t ignore index(idx) where a = 1; mysql> select * from t force index(idx) where a = 1; +------+------+------------------+ | a | b | c | +------+------+------------------+ | 1 | a | {"a": [1, 2, 3]} | | 1 | a | {"a": [1, 2, 3]} | | 1 | a | {"a": [1, 2, 3]} | +------+------+------------------+ 3 rows in set (0.00 sec) mysql> select * from t ignore index(idx) where a = 1; +------+------+------------------+ | a | b | c | +------+------+------------------+ | 1 | a | {"a": [1, 2, 3]} | +------+------+------------------+ 1 row in set (0.00 sec)