Parsing datetime when insert is not compatible with MySQL

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table t1 (t datetime) ENGINE=InnoDB;
insert ignore into t1 values (20030100000000),(20030000000000);
select * from t1;

2. What did you expect to see?
   In Mysql

mysql> create table t1 (t datetime) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> insert ignore into t1 values (20030100000000),(20030000000000);
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select * from t1;
+---------------------+
| t                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

3. What did you see instead?
   In TiDB

mysql> create table t1 (t datetime) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert ignore into t1 values (20030100000000),(20030000000000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+---------------------+
| t                   |
+---------------------+
| 2003-01-00 00:00:00 |
| 2003-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

+---------------------+
| t                   |
+---------------------+
| 2003-01-00 00:00:00 |
| 2003-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-227-g06183393e-dirty
Git Commit Hash: 06183393ed536aa248248b72d4bf9f5010947f66
Git Branch: master
UTC Build Time: 2019-03-15 08:22:53
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[erjiaqing]

For now, INSERT IGNORE is represented as stmt.IgnoreErr, and then

tidb/executor/executor.go

Line 1347 in 0b037aa

sc.IgnoreZeroInDate = !vars.StrictSQLMode || stmt.IgnoreErr || sc.AllowInvalidDate

Maybe we should use sc.InvalidDateAsWarning like

tidb/executor/executor.go

Lines 1342 to 1343 in 0b037aa

	sc.DupKeyAsWarning = stmt.IgnoreErr
	sc.BadNullAsWarning = stmt.IgnoreErr

[ghost]

Confirming this issue still exists in master:

drop table if exists t1;
create table t1 (t datetime) ENGINE=InnoDB;
insert ignore into t1 values (20030100000000),(20030000000000);
select * from t1;
select tidb_version()\G

..

mysql> select * from t1;
+---------------------+
| t                   |
+---------------------+
| 2003-01-00 00:00:00 |
| 2003-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-762-g77aecd4b2
Edition: Community
Git Commit Hash: 77aecd4b27e79a97215eb4fdd68f68f2ddf67d21
Git Branch: master
UTC Build Time: 2020-07-13 01:43:31
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)

[wjhuang2016]

Confirm that this bug had been fixed in master, not in v4.0.8.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (t datetime) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert ignore into t1 values (20030100000000),(20030000000000);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select * from t1;
+---------------------+
| t                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-1735-g8ec67d63b
Edition: Community
Git Commit Hash: 8ec67d63bbcaac5a4da540da1aaf88118c3ca15e
Git Branch: master
UTC Build Time: 2020-12-08 06:06:14
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)

[wjhuang2016]

Fixed by #20206

[ti-srebot]

Please edit this comment or add a new 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

v5.0.0-rc

[ti-srebot]

( component or sig(label) AffectedVersions ) fields are empty.
The values in ( FixedVersions ) fields are incorrect.