Description:
If we insert empty array values into a JSON column, and there's a composite multi-valued index on this column, these rows will be missing in the query result if the index is used.
How to repeat:
create table t(a int, d json, index iad(a, (cast(d->'$.b' as signed array))));
insert into t value(1,'{"b":[]}'), (2,'{"b":[]}');
select * from t use index (iad) where a = 1;
select * from t ignore index (iad) where a = 1;
Result:
127.0.0.1:3306[test]> create table t(a int, d json, index iad(a, (cast(d->'$.b' as signed array))));
Query OK, 0 rows affected (0.024 sec)
127.0.0.1:3306[test]> insert into t value(1,'{"b":[]}'), (2,'{"b":[]}');
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
127.0.0.1:3306[test]> select * from t use index (iad) where a = 1;
Empty set (0.001 sec)
127.0.0.1:3306[test]> select * from t ignore index (iad) where a = 1;
+------+-----------+
| a | d |
+------+-----------+
| 1 | {"b": []} |
+------+-----------+
1 row in set (0.001 sec)
127.0.0.1:3306[test]> select version();
+-----------+
| version() |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.001 sec)
Description: If we insert empty array values into a JSON column, and there's a composite multi-valued index on this column, these rows will be missing in the query result if the index is used. How to repeat: create table t(a int, d json, index iad(a, (cast(d->'$.b' as signed array)))); insert into t value(1,'{"b":[]}'), (2,'{"b":[]}'); select * from t use index (iad) where a = 1; select * from t ignore index (iad) where a = 1; Result: 127.0.0.1:3306[test]> create table t(a int, d json, index iad(a, (cast(d->'$.b' as signed array)))); Query OK, 0 rows affected (0.024 sec) 127.0.0.1:3306[test]> insert into t value(1,'{"b":[]}'), (2,'{"b":[]}'); Query OK, 2 rows affected (0.002 sec) Records: 2 Duplicates: 0 Warnings: 0 127.0.0.1:3306[test]> select * from t use index (iad) where a = 1; Empty set (0.001 sec) 127.0.0.1:3306[test]> select * from t ignore index (iad) where a = 1; +------+-----------+ | a | d | +------+-----------+ | 1 | {"b": []} | +------+-----------+ 1 row in set (0.001 sec) 127.0.0.1:3306[test]> select version(); +-----------+ | version() | +-----------+ | 8.2.0 | +-----------+ 1 row in set (0.001 sec)