TiDB can insert '0000-00-00 00:00:00' in strict sql_mode

[Win-Man]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

MySQL [gangshen]> create table aaa(aaa datetime default null);
Query OK, 0 rows affected (0.15 sec)

MySQL [gangshen]> insert into aaa values('0000-00-00 00:00:00');
Query OK, 1 row affected (0.03 sec)

MySQL [gangshen]> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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.01 sec)

MySQL [gangshen]> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.4
Edition: Community
Git Commit Hash: c61fc7247e9f6bc773761946d5b5294d3f2699a5
Git Branch: heads/refs/tags/v4.0.4
UTC Build Time: 2020-07-31 07:50:19
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

MySQL-5.7.23

MySQL [gangshen]>  create table aaa(aaa datetime default null);
Query OK, 0 rows affected (0.03 sec)

MySQL [gangshen]>  insert into aaa values('0000-00-00 00:00:00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'aaa' at row 1
MySQL [gangshen]> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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)

MySQL [gangshen]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          1209
Current database:       gangshen
Current user:           root@127.0.0.1
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.7.23-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 13 days 23 hours 2 min 58 sec

Threads: 2  Questions: 353959  Slow queries: 3  Opens: 630  Flush tables: 3  Open tables: 20  Queries per second avg: 0.293
--------------

3. What did you see instead (Required)

TiDB can insert '0000-00-00 00:00:00' in strict sql_mode

4. What is your TiDB version? (Required)

MySQL [gangshen]> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.4
Edition: Community
Git Commit Hash: c61fc7247e9f6bc773761946d5b5294d3f2699a5
Git Branch: heads/refs/tags/v4.0.4
UTC Build Time: 2020-07-31 07:50:19
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[ZenoTan]

/label sig/ddl

[ghost]

I believe this is caused by #16740

It looks like common-test needs to be fixed, and then the change can be pushed again.

[SunRunAway]

Duplicate of #19892
#19892 is a superset of this issue, so I'm going to close this issue now, and let's discuss in #19892

[ti-srebot]

Please edit this comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions