Description:
When a table has a compound index defined with a column of 'CAST(column) AS CHAR(15) ARRAY', any rows with [] in JSON are dropped from the results.
Rows that contain NULL or multi-array values return results as expected.
How to repeat:
Create Table
Load Table
Query with NULL
Query with an array with values
Query with empty array [] *PROBLEM*
CREATE TABLE t1(
id INT UNSIGNED NOT NULL,
fk_id INT UNSIGNED NOT NULL,
data JSON DEFAULT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
KEY (fk_id),
KEY (fk_id, (CAST(data AS CHAR(15) ARRAY)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Load up the table with 10,000 rows. See https://gist.github.com/ronaldbradford/493b06fcbb97acd734cb28af769be160
Null value test.
mysql> EXPLAIN SELECT * FROM t1 USE INDEX (functional_index) WHERE fk_id = 23 ORDER BY id DESC LIMIT 10;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | t1 | NULL | ref | functional_index | functional_index | 4 | const | 99 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.02 sec)
mysql> SELECT * FROM t1 USE INDEX (functional_index) WHERE fk_id = 23 ORDER BY id DESC LIMIT 10;
+------+-------+------+----------------------+
| id | fk_id | data | name |
+------+-------+------+----------------------+
| 9894 | 23 | NULL | G3zrgyOfdQunBFcPkhAr |
| 9864 | 23 | NULL | e6tbFNZtL4K5G4TlCs3f |
| 9731 | 23 | NULL | jnA6RLL6VKlGjGQNl1pe |
| 9728 | 23 | NULL | E3FdK88fwOToPrt5S3FK |
| 9458 | 23 | NULL | Tz67m890QQikRIsgvMwJ |
| 9435 | 23 | NULL | qNTP1B4Mtgdw33qpuaEp |
| 9144 | 23 | NULL | zZJ6IGmXoyTFI3rmeyKY |
| 9132 | 23 | NULL | mNIybmN5x9z4ySDQpiX6 |
| 9124 | 23 | NULL | 4d0NTiG95HLl6OQ0NjJu |
| 9033 | 23 | NULL | hlYrc4KH5mDlSZRLB1T3 |
+------+-------+------+----------------------+
10 rows in set (0.01 sec)
Row with array values.
mysql> INSERT INTO t1 VALUES (123456,23,'["x","y","z"]','row with array value');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM t1 USE INDEX (functional_index) WHERE fk_id = 23 ORDER BY id DESC LIMIT 10;
+--------+-------+-----------------+----------------------+
| id | fk_id | data | name |
+--------+-------+-----------------+----------------------+
| 123456 | 23 | ["x", "y", "z"] | row with array value |
| 9894 | 23 | NULL | G3zrgyOfdQunBFcPkhAr |
| 9864 | 23 | NULL | e6tbFNZtL4K5G4TlCs3f |
| 9731 | 23 | NULL | jnA6RLL6VKlGjGQNl1pe |
| 9728 | 23 | NULL | E3FdK88fwOToPrt5S3FK |
| 9458 | 23 | NULL | Tz67m890QQikRIsgvMwJ |
| 9435 | 23 | NULL | qNTP1B4Mtgdw33qpuaEp |
| 9144 | 23 | NULL | zZJ6IGmXoyTFI3rmeyKY |
| 9132 | 23 | NULL | mNIybmN5x9z4ySDQpiX6 |
| 9124 | 23 | NULL | 4d0NTiG95HLl6OQ0NjJu |
+--------+-------+-----------------+----------------------+
10 rows in set (0.01 sec)
Failing test with empty array value []
mysql> SELECT * FROM t1 USE INDEX (functional_index) WHERE fk_id = 23 ORDER BY id DESC LIMIT 10;
+--------+-------+-----------------+----------------------+
| id | fk_id | data | name |
+--------+-------+-----------------+----------------------+
| 123456 | 23 | ["x", "y", "z"] | row with array value |
| 9894 | 23 | NULL | G3zrgyOfdQunBFcPkhAr |
| 9864 | 23 | NULL | e6tbFNZtL4K5G4TlCs3f |
| 9731 | 23 | NULL | jnA6RLL6VKlGjGQNl1pe |
| 9728 | 23 | NULL | E3FdK88fwOToPrt5S3FK |
| 9458 | 23 | NULL | Tz67m890QQikRIsgvMwJ |
| 9435 | 23 | NULL | qNTP1B4Mtgdw33qpuaEp |
| 9144 | 23 | NULL | zZJ6IGmXoyTFI3rmeyKY |
| 9132 | 23 | NULL | mNIybmN5x9z4ySDQpiX6 |
| 9124 | 23 | NULL | 4d0NTiG95HLl6OQ0NjJu |
+--------+-------+-----------------+----------------------+
10 rows in set (0.01 sec)
mysql> SELECT * FROM t1 USE INDEX (fk_id) WHERE fk_id = 23 ORDER BY id DESC LIMIT 10;
+--------+-------+-----------------+----------------------+
| id | fk_id | data | name |
+--------+-------+-----------------+----------------------+
| 123456 | 23 | ["x", "y", "z"] | row with array value |
| 99999 | 23 | [] | row with empty array |
| 9894 | 23 | NULL | G3zrgyOfdQunBFcPkhAr |
| 9864 | 23 | NULL | e6tbFNZtL4K5G4TlCs3f |
| 9731 | 23 | NULL | jnA6RLL6VKlGjGQNl1pe |
| 9728 | 23 | NULL | E3FdK88fwOToPrt5S3FK |
| 9458 | 23 | NULL | Tz67m890QQikRIsgvMwJ |
| 9435 | 23 | NULL | qNTP1B4Mtgdw33qpuaEp |
| 9144 | 23 | NULL | zZJ6IGmXoyTFI3rmeyKY |
| 9132 | 23 | NULL | mNIybmN5x9z4ySDQpiX6 |
+--------+-------+-----------------+----------------------+
10 rows in set (0.01 sec)
Suggested fix:
Index should not drop rows that match.
