MySQL Bugs: #20943: Traditional: INSERT accepts invalid date from Default value

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

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)

Thank you for the bug report.

Abadon the use of "to be fixed later" and setting to status "verified".

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.