Bug #20943 Traditional: INSERT accepts invalid date from Default value
Submitted: 10 Jul 2006 14:46 Modified: 6 May 2014 14:05
Reporter: Kai Ruhnau Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S2 (Serious)
Version:5.0.19/5.0BK/5.1BK OS:Linux (Gentoo Linux)
Assigned to: Assigned Account CPU Architecture:Any

[10 Jul 2006 14:46] Kai Ruhnau
Description:
When a table is created without sql_mode='traditional' or with MySQL before version 5, one can set 0000-00-00 as a default value for date fields.
This default value is inserted in the table even with sql_mode='traditional'

How to repeat:
mysql> SET SESSION sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE default_date (a DATE NOT NULL DEFAULT '0000-00-00');
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO default_date VALUES();
Query OK, 1 row affected (0.03 sec)

mysql> SET SESSION sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO default_date VALUES();
Query OK, 1 row affected (0.02 sec)
-- This should fail as the following does.

mysql> INSERT INTO default_date VALUES('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'a' at row 1
mysql> SELECT * FROM default_date;
+------------+
| a          |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
2 rows in set (0.00 sec)
[18 Jul 2006 16:13] MySQL Verification Team
Thank you for the bug report.
[22 Jan 2014 14:38] Ståle Deraas
Abadon the use of "to be fixed later" and setting to status "verified".
[6 May 2014 14:05] Paul Dubois
Noted in 5.7.5 changelog.

The server failed to produce an error for INSERT statements that
provided no column names but did provide column values.