Bug #110556 When we use json to build an index, we will get wrong results

• Submitted: 30 Mar 2023 2:46
• Modified: 30 Mar 2023 5:27
• Reporter: linkang zhang
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S2 (Serious)
• Version: 8.0.28, 8.0.32
• OS: Linux
• CPU Architecture: Any
• Tags: CAST(), INDEX, json

[30 Mar 2023 2:46] linkang zhang

Description:
When we use json to create a index in the cast() function, we will get a wrong answer when we query using this index.

How to repeat:
// 1. create a table

mysql>  CREATE TABLE test1 (
mysql> `md_id` int NOT NULL AUTO_INCREMENT,
mysql> `member_id` int NOT NULL,
mysql> `mid` int NOT NULL,
mysql> `expand_info` json NOT NULL,
mysql> PRIMARY KEY (`md_id`) USING BTREE,
mysql> UNIQUE INDEX `member_id`(`mid` ASC, `member_id` ASC) USING BTREE
mysql> ) ENGINE = InnoDB;

Query OK, 0 rows affected (0.01 sec)

// 2. insert some data into the table
mysql> INSERT INTO test1 VALUES (1, 1, 1, "[1]");

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test1 VALUES (2, 2, 1, "[]");

Query OK, 1 row affected (0.01 sec)

// 3. create an index using cast() and json

mysql> ALTER TABLE test1
mysql> ADD INDEX test_index (
mysql> mid, (CAST(expand_info AS CHAR(20) array))
mysql> );

Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

// 4. select the count of data,and we get wrong answer

mysql> SELECT * FROM test1;

+-------+-----------+-----+-------------+
| md_id | member_id | mid | expand_info |
+-------+-----------+-----+-------------+
|     1 |         1 |   1 | [1]         |
|     2 |         2 |   1 | []          |
+-------+-----------+-----+-------------+
2 rows in set (0.00 sec)

mysql> SELECT count(*) FROM test1 force index (test_index) where mid = 1;

+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

// 5. end, we get the wrong answer

[30 Mar 2023 5:27] MySQL Verification Team

Hello linkang zhang,

Thank you for the report and test case.

regards,
Umesh

[25 Mar 20:22] Jean-François Gagné

I am curious how much this is related to Bug#120149...

And I would like to add my disappointment that this bug is still unfixed in 8.0.45, 8.4.8 and 9.6.0.

./use test <<< '
DROP TABLE IF EXISTS Test1;
CREATE TABLE test1 (
  `md_id` int NOT NULL AUTO_INCREMENT,
  `member_id` int NOT NULL,
  `mid` int NOT NULL,
  `expand_info` json NOT NULL,
  PRIMARY KEY (`md_id`) USING BTREE,
  UNIQUE INDEX `member_id`(`mid` ASC, `member_id` ASC) USING BTREE
) ENGINE = InnoDB;

ALTER TABLE test1 ADD INDEX test_index (mid, (CAST(expand_info AS CHAR(20) array)));

INSERT INTO test1 VALUES (1, 1, 1, "[1]"),  (2, 2, 1, "[]");

SELECT version();
SELECT * FROM test1;
SELECT count(*) FROM test1 force index (Primary) where mid = 1;
SELECT count(*) FROM test1 force index (test_index) where mid = 1;'

version()
8.0.45
md_id	member_id	mid	expand_info
1	1	1	[1]
2	2	1	[]
count(*)
2
count(*)
1

version()
8.4.8
md_id	member_id	mid	expand_info
1	1	1	[1]
2	2	1	[]
count(*)
2
count(*)
1

version()
9.6.0
md_id	member_id	mid	expand_info
1	1	1	[1]
2	2	1	[]
count(*)
2
count(*)
1