can't insert data if date multi-value index is used

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t45ebe76a` (   `col_24` varchar(109) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,   `col_25` char(142) COLLATE gbk_bin DEFAULT NULL,   `col_26` json NOT NULL,   `col_27` varchar(73) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,   `col_28` json NOT NULL,   `col_29` binary(24) DEFAULT NULL,   `col_30` text DEFAULT NULL,   `col_31` timestamp NULL DEFAULT NULL,   `col_32` text DEFAULT NULL,   UNIQUE KEY `idx_9` ((cast(`col_26` as date array)),`col_29`),   PRIMARY KEY (`col_24`(2))  ,   UNIQUE KEY `idx_11` ((cast(`col_26` as date array))) ) ENGINE=InnoDB DEFAULT
CHARSET=gbk COLLATE=gbk_chinese_ci;
insert  into t45ebe76a set col_24 = '%rC!NV~5b', col_25 = 'c', col_26 = '["1977-03-22","1983-09-23","1971-06-18","2005-06-13","1999-09-30"]', col_27 = 't#f1FWRtyyGkjY', col_28 = '[\"BE2TkC8X6QiQjeQbbCkDMNrg8Ap330f9vUUPEcOexb0XdjOkZyNbpC1UsBn5tgUb\"]', col_29 = '@s#11e9AwR0*p', col_30 = '8sjIbt!h6', col_31 = '1984-03-19', col_32 = '靗4C樲GS*X朿勑梾饨&x$T舐';

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

No error

3. What did you see instead (Required)

mysql> insert  into t45ebe76a set col_24 = '%rC!NV~5b', col_25 = 'c', col_26 = '["1977-03-22","1983-09-23","1971-06-18","2005-06-13","1999-09-30"]', col_27 = 't#f1FWRtyyGkjY', col_28 = '[\"BE2TkC8X6QiQjeQbbCkDMNrg8Ap330f9vUUPEcOexb0XdjOkZyNbpC1UsBn5tgUb\"]', col_29 = '@s#11e9AwR0*p', col_30 = '8sjIbt!h6', col_31 = '1984-03-19', col_32 = '靗4C樲GS*X朿勑梾饨&x$T舐';
ERROR 3903 (HY000): Invalid JSON value for CAST for expression index 'idx_9'

4. What is your TiDB version? (Required)

56c619f1f5ea8e3f9970fe664729074b5123683a

[YangKeao]

This problem is more frightening than I have thought. The auto-conversion between types (string <-> date <-> datetime) can cause very confusing behavior. For example:

create table t2(j json, key mvi((cast(j as date array))) );
insert into t2 values ('["2024-01-15 13:19:00"]');
insert into t2 values ('["2024-01-15"]');
select * from t2 ignore index(mvi) where '2024-01-15' member of(j);
select * from t2 use index(mvi) where '2024-01-15' member of(j);

The last two SELECT statements will have different behaviors on MySQL:

mysql> select * from t2 ignore index(mvi) where '2024-01-15' member of(j);
+----------------+
| j              |
+----------------+
| ["2024-01-15"] |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t2 use index(mvi) where '2024-01-15' member of(j);
+-------------------------+
| j                       |
+-------------------------+
| ["2024-01-15 13:19:00"] |
| ["2024-01-15"]          |
+-------------------------+
2 rows in set (0.00 sec)

As TiDB has a more strict type restriction, we will not trigger this problem. I prefer to leave this issue open and not fix it for a while, as solving it in the most straight forward way will cause more serious issue.

I've also submitted a bug report to MySQL: https://bugs.mysql.com/bug.php?id=113641 . Let's wait for the response of MySQL team and see how they will fix it :thinking: .