TiDB does not handle NO_ZERO_IN_DATE correctly in cast

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

> set @@sql_mode='NO_ZERO_IN_DATE';
> select cast('2022-01-00' as date);

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

In Mysql8.0:

mysql> set @@sql_mode='NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select cast('2022-01-00' as date);
+----------------------------+
| cast('2022-01-00' as date) |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

In TiDB:

mysql> set @@sql_mode='NO_ZERO_IN_DATE';
Query OK, 0 rows affected (0.00 sec)

mysql> select cast('2022-01-00' as date);
+----------------------------+
| cast('2022-01-00' as date) |
+----------------------------+
| 2022-01-00                 |
+----------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

[zanmato1984]

NO_ZERO_IN_DATE is deprecated in MySQL 8.0. And the reason is that MySQL thinks it should be used in conjunction with strict mode (hence the deprecation).

Meanwhile in TiDB, if used with strict mode, insertion of date values with 0 month or day behaves the same as MySQL, though simple selection does not:

tidb> set @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
Query OK, 0 rows affected (0.00 sec)

tidb> select cast('2022-01-00' as date);
+----------------------------+
| cast('2022-01-00' as date) |
+----------------------------+
| 2022-01-00                 |
+----------------------------+
1 row in set (0.00 sec)

tidb> insert into t values(cast('2022-01-00' as date));
ERROR 1292 (22007): Incorrect date value: '2022-01-00' for column 'i' at row 1

So I'm lowering the severity to moderate.