MySQL Bugs: #5933: Traditional: zero dates should be rejected

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

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)

Verified against latest BK source tree.

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.

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

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

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