MySQL 8.0 and wrong dates

Facebook

Twitter

Linkedin

Reddit

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.

Facebook

Twitter

Linkedin

Reddit