incompatible behavior with MySQL in JOIN regarding invalid date field

[eurekaka]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

First, prepare data using following SQL script:

DROP TABLE IF EXISTS `table100_int_autoinc`;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
CREATE TABLE `table100_int_autoinc` (
  `col_date` date DEFAULT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_unsigned_not_null` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `table100_int_autoinc` VALUES ('2009-08-03',6,1,6);
INSERT INTO `table100_int_autoinc` VALUES (NULL,7,2,8);
INSERT INTO `table100_int_autoinc` VALUES ('2009-02-08',8,3,4);
INSERT INTO `table100_int_autoinc` VALUES ('2010-00-20',4,4,3);
INSERT INTO `table100_int_autoinc` VALUES ('2001-12-04',9,51,9);
INSERT INTO `table100_int_autoinc` VALUES ('2004-01-03',7,52,2);

the SQL_MODE must be set to insert the invalid forth row into table.

Then, we can see result is different with MySQL for query:

SELECT * FROM `table100_int_autoinc` `t1` JOIN `table100_int_autoinc` `t2` ON `t1`.`col_int_unsigned_not_null` = `t2`.`col_int_not_null_key` ORDER BY `t1`.`pk`, `t2`.`pk` ;

2. What did you expect to see?

In MySQL, result is:

mysql> SELECT * FROM `table100_int_autoinc` `t1` JOIN `table100_int_autoinc` `t2` ON `t1`.`col_int_unsigned_not_null` = `t2`.`col_int_not_null_key` ORDER BY `t1`.`pk`, `t2`.`pk` ;
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
| col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null | col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null |
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
| 2009-08-03 |                    6 |  1 |                         6 | 2009-08-03 |                    6 |  1 |                         6 |
| NULL       |                    7 |  2 |                         8 | 2009-02-08 |                    8 |  3 |                         4 |
| 2009-02-08 |                    8 |  3 |                         4 | 0000-00-00 |                    4 |  4 |                         3 |
| 2001-12-04 |                    9 | 51 |                         9 | 2001-12-04 |                    9 | 51 |                         9 |
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '2010-00-20' for column 'col_date' at row 7 |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead?

In TiDB, result is:

mysql> SELECT * FROM `table100_int_autoinc` `t1` JOIN `table100_int_autoinc` `t2` ON `t1`.`col_int_unsigned_not_null` = `t2`.`col_int_not_null_key` ORDER BY `t1`.`pk`, `t2`.`pk` ;
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
| col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null | col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null |
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
| 2009-08-03 |                    6 |  1 |                         6 | 2009-08-03 |                    6 |  1 |                         6 |
| NULL       |                    7 |  2 |                         8 | 2009-02-08 |                    8 |  3 |                         4 |
| 2009-02-08 |                    8 |  3 |                         4 | 2010-00-20 |                    4 |  4 |                         3 |
| 2001-12-04 |                    9 | 51 |                         9 | 2001-12-04 |                    9 | 51 |                         9 |
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
4 rows in set (0.00 sec)

mysql> select version();
+--------------------------------------+
| version()                            |
+--------------------------------------+
| 5.7.10-TiDB-v2.1.0-rc.3-147-g379ee5b |
+--------------------------------------+
1 row in set (0.00 sec)

Note that second col_date field of the third result row is different, because MySQL notices the value is invalid for date type, and convert it to zero, while TiDB does not.

One thing worth mention is that, MySQL does not always notice the invalidation, for example:

mysql> SELECT * FROM `table100_int_autoinc`;
+------------+----------------------+----+---------------------------+
| col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null |
+------------+----------------------+----+---------------------------+
| 2009-08-03 |                    6 |  1 |                         6 |
| NULL       |                    7 |  2 |                         8 |
| 2009-02-08 |                    8 |  3 |                         4 |
| 2010-00-20 |                    4 |  4 |                         3 |
| 2001-12-04 |                    9 | 51 |                         9 |
| 2004-01-03 |                    7 | 52 |                         2 |
+------------+----------------------+----+---------------------------+
6 rows in set (0.00 sec)

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

mysql> select version();
+--------------------------------------+
| version()                            |
+--------------------------------------+
| 5.7.10-TiDB-v2.1.0-rc.3-147-g379ee5b |
+--------------------------------------+
1 row in set (0.00 sec)

SIG slack channel

#sig-exec

Score

300

Mentor

• @lzmhhh123

[ghost]

I couldn't initially reproduce in MySQL, but then noticed the test case doesn't reset the default sql_mode after changing it. Here is a slightly clearer test case which shows this issue is still present in TiDB:

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; -- set to enable insert
DROP TABLE IF EXISTS `table100_int_autoinc`;
CREATE TABLE `table100_int_autoinc` (
  `col_date` date DEFAULT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_unsigned_not_null` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `table100_int_autoinc` VALUES ('2009-08-03',6,1,6);
INSERT INTO `table100_int_autoinc` VALUES (NULL,7,2,8);
INSERT INTO `table100_int_autoinc` VALUES ('2009-02-08',8,3,4);
INSERT INTO `table100_int_autoinc` VALUES ('2010-00-20',4,4,3);
INSERT INTO `table100_int_autoinc` VALUES ('2001-12-04',9,51,9);
INSERT INTO `table100_int_autoinc` VALUES ('2004-01-03',7,52,2);
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- restore default
SELECT * FROM `table100_int_autoinc` `t1` JOIN `table100_int_autoinc` `t2` ON `t1`.`col_int_unsigned_not_null` = `t2`.`col_int_not_null_key` ORDER BY `t1`.`pk`, `t2`.`pk` ;
SELECT tidb_version()\G

..

mysql> SELECT * FROM `table100_int_autoinc` `t1` JOIN `table100_int_autoinc` `t2` ON `t1`.`col_int_unsigned_not_null` = `t2`.`col_int_not_null_key` ORDER BY `t1`.`pk`, `t2`.`pk` ;
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
| col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null | col_date   | col_int_not_null_key | pk | col_int_unsigned_not_null |
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
| 2009-08-03 |                    6 |  1 |                         6 | 2009-08-03 |                    6 |  1 |                         6 |
| NULL       |                    7 |  2 |                         8 | 2009-02-08 |                    8 |  3 |                         4 |
| 2009-02-08 |                    8 |  3 |                         4 | 2010-00-20 |                    4 |  4 |                         3 |
| 2001-12-04 |                    9 | 51 |                         9 | 2001-12-04 |                    9 | 51 |                         9 |
+------------+----------------------+----+---------------------------+------------+----------------------+----+---------------------------+
4 rows in set (0.01 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-745-g2b0b34b88
Edition: Community
Git Commit Hash: 2b0b34b88e43ad20f4e5ab1a0b5daf7ae6ff6046
Git Branch: master
UTC Build Time: 2020-07-09 10:12:32
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)

[xuhui-lu]

@eurekaka Is this issue still open?

[wjhuang2016]

@eurekaka Is this issue still open?

@xuhui-lu Of course, it's still open. Would you like to fix it?

[solotzg]

Now, the behavior of TiDB and MySQL are the same.

CREATE TABLE `table100_int_autoinc` (
  `col_date` date DEFAULT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_unsigned_not_null` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `table100_int_autoinc` VALUES ('2009-08-03',6,1,6);
INSERT INTO `table100_int_autoinc` VALUES (NULL,7,2,8);
INSERT INTO `table100_int_autoinc` VALUES ('2009-02-08',8,3,4);
INSERT INTO `table100_int_autoinc` VALUES ('2010-00-20',4,4,3);
INSERT INTO `table100_int_autoinc` VALUES ('2001-12-04',9,51,9);
INSERT INTO `table100_int_autoinc` VALUES ('2004-01-03',7,52,2);

SELECT * FROM `table100_int_autoinc` `t1` JOIN `table100_int_autoinc` `t2` ON `t1`.`col_int_unsigned_not_null` = `t2`.`col_int_not_null_key` ORDER BY `t1`.`pk`, `t2`.`pk` ;

show warnings;

SELECT VERSION();

col_date	col_int_not_null_key	pk	col_int_unsigned_not_null	col_date	col_int_not_null_key	pk	col_int_unsigned_not_null
2009-08-03	6	1	6	2009-08-03	6	1	6
NULL	7	2	8	2009-02-08	8	3	4
2009-02-08	8	3	4	2010-00-20	4	4	3
2001-12-04	9	51	9	2001-12-04	9	51	9
VERSION()
8.0.28-0ubuntu0.20.04.3

Shall we close it?