another DATETIME's behaviour is different from mysql

[9547]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

similar to #11329

1. Minimal reproduce step (Required)

select date_add('2001-01-00', interval -2 hour);

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

in MySQL 8.0:

mysql root@127.0.0.1:app> select date_add('2001-01-00', interval -2 hour);
+------------------------------------------+
| date_add('2001-01-00', interval -2 hour) |
+------------------------------------------+
| <null>                                   |
+------------------------------------------+
1 row in set
Time: 0.021s
mysql root@127.0.0.1:app> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2001-01-00' |
+---------+------+----------------------------------------+

3. What did you see instead (Required)

in TiDB

mysql root@127.0.0.1:(none)> select date_add('2001-00-01', interval -2 hour);
+------------------------------------------+
| date_add('2001-00-01', interval -2 hour) |
+------------------------------------------+
| 2000-11-30 22:00:00                      |
+------------------------------------------+

4. What is your TiDB version? (Required)

mysql root@127.0.0.1:(none)> select tidb_version() \G;
***************************[ 1. row ]***************************
tidb_version() | Release Version: v4.0.0-beta.2-1165-g726341146-dirty
Edition: Community
Git Commit Hash: 726341146b99485335a195b16a803b95e4875228
Git Branch: master
UTC Build Time: 2020-09-08 01:55:13
GoVersion: go1.14.3
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

1 row in set
Time: 0.009s

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @lzmhhh123

[wshwsh12]

/label sig/execution

[9547]

@wshwsh12 Seems it's related to the server mode of NO_ZERO_IN_DATE and found another incompatible issue of NO_ZERO_DATE see detail from #19892

[xiaodong-ji]

/pick-up

[ti-challenge-bot]

Pick up success.

[ichn-hu]

@xiaodong-ji Hi, do you have any updates on this?

[xiaodong-ji]

@xiaodong-ji Hi, do you have any updates on this?

The PR is waiting to be reviewed.