MySQL 8.0 and wrong dates

In MySQL 8.0, when using invalid values for date data types, an error is returned. This was not the case in 5.x versions.

Let’s have a look using the table definition of bug 96361:

CREATE TABLE `new_table` (
  `id_table` int(11) NOT NULL AUTO_INCREMENT,
  `text_table` varchar(45) DEFAULT NULL,
  `date_table` date DEFAULT NULL,
  PRIMARY KEY (`id_table`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now we can try the following statement in MySQL 5.7 and MySQL 8.0:

MySQL 5.7.26> SELECT id_table, text_table 
              FROM new_table WHERE date_table = '' OR date_table IS NULL;
Empty set, 1 warning (0.01 sec)

MySQL 5.7.26> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '' for column 'date_table' at row 1 |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0.17> SELECT id_table, text_table 
              FROM new_table WHERE date_table = '' OR date_table IS NULL;
ERROR 1525 (HY000): Incorrect DATE value: ''

We can see that in MySQL 5.7, a warning is returned but no error.

In earlier version of 5.x it was by default also possible to store DATEs as 0000-00-00. This is not possible anymore neither in 5.7, neither in 8.0 (by default):

mysql> insert into new_table (text_table, date_table) values ('lefred','0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_table' at row 1

To be able to use 0000-00-00 as date the SQL_MODE need to be changed. By default it contains NO_ZERO_IN_DATE,NO_ZERO_DATE.

mysql8> set @@SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql8> select @@SQL_MODE\G
*************************** 1. row ***************************
@@SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql8> insert into new_table (text_table, date_table) values ('lefred','0000-00-00');
Query OK, 1 row affected (0.04 sec)

But even when this is changes, invalid dates (other than invalid 0‘s)are of course still considered as errors in MySQL 8.0:

mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '';
ERROR 1525 (HY000): Incorrect DATE value: ''
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
|        2 | lefred     |
+----------+------------+
1 row in set (0.00 sec)

mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-00-01';
Empty set (0.00 sec)

mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-01-32';
ERROR 1525 (HY000): Incorrect DATE value: '2000-01-32'
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '0000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
|        1 | lefred     |
|        3 | lefred     |
+----------+------------+
2 rows in set (0.00 sec)

This is because when comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. Before 8.0 (8.0.16) when the conversion failed, MySQL executed the comparison treating the DATE as a string. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE.