conversion from zero int to time is not compatible with MySQL

[tangenta]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Make sure sql_mode contains "NO_ZERO_DATE".

drop table if exists t;
create table t (a int);
insert into t values (0);
alter table t modify column a date;

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

mysql> alter table t modify column a date;
ERROR 1292 (22007): Incorrect date value: '0' for column 'a' at row 1

3. What did you see instead (Required)

mysql> alter table t modify column a date;
Query OK, 0 rows affected (0.58 sec)

mysql> select * from t;
+------------+
| a          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

master

commit f81ef5579551a0523d18b049eb25ab3375bcfb48 (HEAD, upstream/master)
Author: Yuanjia Zhang <zhangyuanjia@pingcap.com>
Date:   Tue Jun 8 20:08:28 2021 +0800

    planner: solve an incompatible issue about dynamic-mode and TiFlash and enable dynamic mode by default  (#25255)

[tangenta]

Here are MySQL 8.0 behaviors of casting from zero integer to time-related type:

• cast in DQL(select date(0))
• cast in DDL(alter table t modify column a date)

set sql_mode='';
select date(0);
select time(0);
select timestamp(0);

sql_mode	date	datetime	timestamp
‘’(empty)	0000-00-00	00:00:00	0000-00-00 00:00:00
strict_trans_tables	0000-00-00	00:00:00	0000-00-00 00:00:00
allow_invalid_dates	0000-00-00	00:00:00	0000-00-00 00:00:00
no_zero_in_date	0000-00-00	00:00:00	0000-00-00 00:00:00
no_zero_date	NULL	00:00:00	NULL

drop table if exists t;
create table t (a int);
insert into t values (0);
alter table t modify column a date;
alter table t modify column a datetime;
alter table t modify column a timestamp;

sql_mode	date	datetime	timestamp
‘’(empty)	warning(Out of range value)	warning(Out of range value)	warning(Out of range value)
strict_trans_tables	error(Incorrect date value)	error(Incorrect date value)	error(Incorrect date value)
allow_invalid_dates	warning(Out of range value)	warning(Out of range value)	warning(Out of range value)
no_zero_in_date	warning(Out of range value)	warning(Out of range value)	warning(Out of range value)
no_zero_date	warning(Out of range value)	warning(Out of range value)	warning(Out of range value)

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.