You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATETABLEt ( id INTPRIMARY KEY, j JSON, INDEX idx_a ((CAST(j->'$.a'ASDATE ARRAY))) );
INSERT INTO t VALUES (1, JSON_OBJECT('a', JSON_ARRAY('2024-05-13', '2020-01-01')));
2. What did you expect to see? (Required)
mysql> CREATE TABLE t ( id INTPRIMARY KEY, j JSON, INDEX idx_a ((CAST(j->'$.a'ASDATE ARRAY))) );
Query OK, 0 rows affected (0.02 sec)
mysql>INSERT INTO t VALUES (1, JSON_OBJECT('a', JSON_ARRAY('2024-05-13', '2020-01-01')));
Query OK, 1 row affected (0.01 sec)
3. What did you see instead (Required)
mysql> CREATE TABLE t ( id INTPRIMARY KEY, j JSON, INDEX idx_a ((CAST(j->'$.a'ASDATE ARRAY))) );
Query OK, 0 rows affected (0.11 sec)
mysql>INSERT INTO t VALUES (1, JSON_OBJECT('a', JSON_ARRAY('2024-05-13', '2020-01-01')));
ERROR 3903 (HY000): Invalid JSON value for CAST for expression index 'idx_a'
A workaround is manually casting the type to date:
mysql>INSERT INTO t VALUES (1, JSON_OBJECT('a', JSON_ARRAY(cast('2024-05-13'asdate))));
Query OK, 1 row affected (0.01 sec)
4. What is your TiDB version? (Required)
mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:15
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
The text was updated successfully, but these errors were encountered:
Thanks for the information. I remembered that I also submitted a similar bug report in the comment #50370 (comment), bug report.
I thought that the implicitly casting doesn't mean we'll introduce this bug (as long as the planner works well). But now, I agree to hold this PR for a while and see whether MySQL will change its behavior or not 🤔. As this issue has a workaround, it'll not be a big problem for the users.
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
3. What did you see instead (Required)
A workaround is manually casting the type to date:
4. What is your TiDB version? (Required)
The text was updated successfully, but these errors were encountered: