Bug #108640 multi-valued index with composite index get wrong result

Description:
if we define a table that contains a composite index that contains a multi-valued index, it will get the wrong result whining using it.

How to repeat:
create table t(a int, b char(10), c json, index idx(a, b, (cast(c->'$.a' as unsigned Array))));
insert into t values (1, 'a', '{"a": [1, 2, 3]}'), (2, 'b', '{"a": [4, 5, 6]}');
select * from t force index(idx) where a = 1;
select * from t ignore index(idx) where a = 1;

mysql> select * from t force index(idx) where a = 1;
+------+------+------------------+
| a    | b    | c                |
+------+------+------------------+
|    1 | a    | {"a": [1, 2, 3]} |
|    1 | a    | {"a": [1, 2, 3]} |
|    1 | a    | {"a": [1, 2, 3]} |
+------+------+------------------+
3 rows in set (0.00 sec)

mysql> select * from t ignore index(idx) where a = 1;
+------+------+------------------+
| a    | b    | c                |
+------+------+------------------+
|    1 | a    | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)

Hello x j,

Thank you for the report and test case.
Imho this issue is fixed in 8.0.29+, could you please confirm if you are seeing the issue in latest GA? Thank you.

- 8.0.29+ not affected

bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t(a int, b char(10), c json, index idx(a, b, (cast(c->'$.a' as unsigned Array))));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t values (1, 'a', '{"a": [1, 2, 3]}'), (2, 'b', '{"a": [4, 5, 6]}');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t force index(idx) where a = 1;
+------+------+------------------+
| a    | b    | c                |
+------+------+------------------+
|    1 | a    | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)

mysql> select * from t ignore index(idx) where a = 1;
+------+------+------------------+
| a    | b    | c                |
+------+------+------------------+
|    1 | a    | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)

- 8.0.28 affected
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> create table t(a int, b char(10), c json, index idx(a, b, (cast(c->'$.a' as unsigned Array))));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (1, 'a', '{"a": [1, 2, 3]}'), (2, 'b', '{"a": [4, 5, 6]}');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t force index(idx) where a = 1;
+------+------+------------------+
| a    | b    | c                |
+------+------+------------------+
|    1 | a    | {"a": [1, 2, 3]} |
|    1 | a    | {"a": [1, 2, 3]} |
|    1 | a    | {"a": [1, 2, 3]} |
+------+------+------------------+
3 rows in set (0.00 sec)

mysql> select * from t ignore index(idx) where a = 1;
+------+------+------------------+
| a    | b    | c                |
+------+------+------------------+
|    1 | a    | {"a": [1, 2, 3]} |
+------+------+------------------+
1 row in set (0.00 sec)

regards,
Umesh

great， thinks