In this blog post, we explore how MySQL 8.0 handles dates that are input incorrectly. This is an important topic to understand, as incorrect dates behaves differently between MySQL 5.x and MySQL 8.0.

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.40> 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.40> 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.31> 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 ('all zeros','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)

SQL_MODE is part of the variables that can be changed via a new configuration in MySQL DB Instance in OCI:

OCI MySQL DB Configuration


mysql8> INSERT INTO new_table (text_table, date_table) VALUES ('all zeros','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.

Let’s add some more records:

mysql8> INSERT INTO new_table (text_table, date_table) 
        VALUES ('good','2022-06-16'), ('one zero','2022-06-00'); 
Query OK, 2 rows affected (0.0082 sec)

mysql8> SELECT id_table, text_table FROM new_table WHERE date_table = '';
ERROR 1525 (HY000): Incorrect DATE value: ''

mysql8> SELECT id_table, text_table FROM new_table WHERE date_table = '2022-06-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
|        3 | one zero   |
+----------+------------+
1 row in set (0.00 sec)

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

mysql8> SELECT id_table, text_table FROM new_table 
        WHERE date_table = '2000-01-32';
ERROR 1525 (HY000): Incorrect DATE value: '2000-01-32'

mysql8> SELECT id_table, text_table FROM new_table 
        WHERE date_table = '0000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
|        1 | all zeros  |
+----------+------------+
1 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.

In conclusion, it is important to be careful when inputting dates into MySQL 8.0 as it can behave differently with invalid dates and returning errors. Always make sure to double check your dates to ensure they are in the correct format and avoid any potential errors or unexpected behavior in your queries.  Overall, the default in MySQL 8.0 and in MySQL HeatWave on OCI makes more sense, allowing wrong dates could lead to potential issues anyway.