Bug #112147 Use/Ignore Mutil-value index for MEMBER OF function has differnet result

• Submitted: 23 Aug 2023 2:58
• Modified: 23 Aug 2023 7:00
• Reporter: Shenghui Wu
• Status: Verified
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.34
• OS: Any
• CPU Architecture: Any

[23 Aug 2023 2:58] Shenghui Wu

Description:
Use/Ignore Mutil-value index for MEMBER OF function has differnet result

How to repeat:
MySQL [test]> use test;
Database changed
MySQL [test]> create table t(a int, j json, index kj((cast(j as date array))));

Query OK, 0 rows affected (0.178 sec)

MySQL [test]> insert into t values(1, json_array(cast("2010-10-10" as date), cast("2010-10-10" as date)));
Query OK, 1 row affected (0.024 sec)

MySQL [test]> select * from t use index(kj) where "2010-10-10" member of(j);
+------+------------------------------+
| a    | j                            |
+------+------------------------------+
|    1 | ["2010-10-10", "2010-10-10"] |
+------+------------------------------+
1 row in set (0.000 sec)

MySQL [test]> select * from t ignore index(kj) where "2010-10-10" member of(j);
Empty set (0.000 sec)

[23 Aug 2023 7:00] MySQL Verification Team

Hello Shenghui Wu,

Thank you for the report and test case.
Verified as described. 

regards,
Umesh

[24 Aug 2023 7:31] Roy Lyseng

Workaround: Use a proper DATE literal value, such as in

  select * from t ignore index(kj) where DATE'2010-10-10' member of(j);