Bug #5933 Traditional: zero dates should be rejected
Submitted: 6 Oct 2004 18:44 Modified: 1 Apr 2005 11:53
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.2-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Michael Widenius CPU Architecture:Any

[6 Oct 2004 18:44] Trudy Pelzer
Description:
When sql_mode='traditional', zero dates and timestamps
must be rejected. But they are still being accepted.

How to repeat:
mysql> set sql_mode='traditional';

mysql> create table t1 (col1 date, col2 time, col3 timestamp);
Query OK, 0 rows affected (0.29 sec)

mysql> insert into t1 values (0,0,0);
Query OK, 1 row affected (0.00 sec)
-- This is the incorrect response. The INSERT should be
rejected with SQLSTATE 22007 invalid datetime format.

mysql> select * from t17;
+------------+----------+---------------------+
| col1       | col2     | col3                |
+------------+----------+---------------------+
| 0000-00-00 | 00:00:00 | 0000-00-00 00:00:00 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
[6 Oct 2004 19:00] MySQL Verification Team
Verified against latest BK source tree.
[29 Oct 2004 1:25] Trudy Pelzer
Another test case for this:

set sql_mode='';
drop table if exists t1;

mysql> create table t1 (col1 date);

mysql> insert into t1 values ('2004-02-30');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t1;
+------------+
| col1           |
+------------+
| 0000-00-00 |
+------------+

mysql> set sql_mode='traditional';

mysql> insert into t1 select * from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
-- This is the incorrect response, because the date being inserted
is a zero date. The INSERT should fail.

mysql> select * from t1;
+------------+
| col1          |
+------------+
| 0000-00-00 |
| 0000-00-00 |
+------------+
-- The table should have only one row.
[1 Apr 2005 11:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23562
[1 Apr 2005 11:53] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 5.0.4
[25 Aug 2009 14:51] Fábio Castro
What if a user inserts a valid date in a database and then decides he wants to remove the date?

If the user only has a textbox at his disposal he will not be able to choose null option.

How do we do it users take this kind of action?

Thank you