Can not create date column with a default value '0'

[GMHDBJD]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE db.tb ADD COLUMN `expect_date` DATE NOT NULL DEFAULT 0;
ERROR 1067 (42000): Invalid default value for 'expect_date'

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

MySQL 5.7.35

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE shardddl1.tb1 ADD COLUMN `expect_date1` DATE NOT NULL DEFAULT 0;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

master

[JayLZhou]

/assign

[JayLZhou]

/unassign

[Defined2014]

It fixed by #30507 and works in master branch, but still not compatible with MySQL in some statements, like

create table t(a date not null default '0');
create table t(a timestamp not null default '0');

The reason when type of default value is string, TiDB will use parseDatetime function which only support format like 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' to parse it. And the behavior in MySQL is very strange, it supports '0', '0.0', '0.001' as timestamp in default value, but not support '1'. So maybe we should follow it now. Change it to compatibility issue.