Current MV index maintenance logic will cause wrong query result on empty array values

[time-and-fate]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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;

2. What did you expect to see? (Required)

> select * from t use index (iad) where a = 1;
+------+-----------+
| a    | d         |
+------+-----------+
|    1 | {"b": []} |
+------+-----------+

> select * from t ignore index (iad) where a = 1;
+------+-----------+
| a    | d         |
+------+-----------+
|    1 | {"b": []} |
+------+-----------+

3. What did you see instead (Required)

> select * from t use index (iad) where a = 1;
Empty set (0.002 sec)

> select * from t ignore index (iad) where a = 1;
+------+-----------+
| a    | d         |
+------+-----------+
|    1 | {"b": []} |
+------+-----------+
1 row in set (0.001 sec)

> explain analyze select * from t use index (iad) where a = 1;
+-------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
| id                            | estRows | actRows | task      | access object                                                              | execution info                                                                                                                                                                                                                                                                                            | operator info                               | memory    | disk |
+-------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+
| IndexMerge_7                  | 10.00   | 0       | root      |                                                                            | time:606.4µs, loops:1                                                                                                                                                                                                                                                                                     | type: union                                 | 270 Bytes | N/A  |
| ├─IndexRangeScan_5(Build)     | 10.00   | 0       | cop[tikv] | table:t, index:iad(a, cast(json_extract(`d`, _utf8'$.b') as signed array)) | time:480.1µs, loops:1, cop_task: {num: 1, max: 409.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 384.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 33.9µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 20µs, rocksdb: {block: {}}}      | range:[1,1], keep order:false, stats:pseudo | N/A       | N/A  |
| └─TableRowIDScan_6(Probe)     | 10.00   | 0       | cop[tikv] | table:t                                                                    |                                                                                                                                                                                                                                                                                                           | keep order:false, stats:pseudo              | N/A       | N/A  |
+-------------------------------+---------+---------+-----------+----------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+-----------+------+

4. What is your TiDB version? (Required)

From v6.6.0 to the latest master (v7.6.0)

[time-and-fate]

If I understand it correctly, currently we won't create any index entries for the case above, which is the root cause of the bug.

Interestingly, MySQL has the same problem.