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
