Bug #5902 Traditional mode: STR_TO_DATE changes invalid value rather than rejecting
Submitted: 5 Oct 2004 17:15 Modified: 30 Mar 2005 22:35
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

[5 Oct 2004 17:15] Trudy Pelzer
Description:
When sql_mode='traditional', invalid date values must be 
rejected, rather than silently changed to another value. 
The default behaviour of the STR_TO_DATE function is 
to silently change invalid temporal values to NULL. This 
behaviour is still happening using 'traditional' mode. That 
is: 
CREATE TABLE t1 (col1 DATETIME) ENGINE=INNODB; 
INSERT INTO t1 VALUES (STR_TO_DATE ('32.10.2004 15.30','%d.%m.%Y %H.%i')); 
should return SQLSTATE 22007 Incorrect datetime value: '2004-10-32 15:30' ... 
Instead, it inserts NULL into the table. 
 
 

How to repeat:
mysql> set sql_mode='traditional'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> create table t1 (col1 datetime); 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into t1 values('2004-10-32 10:15'); 
ERROR 1292 (22007): Incorrect datetime value: '2004-10-32 10:15' for column 'col1' at row 1 
-- This is the correct response. 
 
mysql> select * from t1; 
Empty set (0.00 sec) 
 
mysql> insert into t1 values (STR_TO_DATE('32.10.2004 15.30','%d.%m.%Y %H.%i')); 
Query OK, 1 row affected (0.00 sec) 
-- This is the incorrect response; the INSERT should be rejected. 
 
mysql> select * from t1; 
+------+ 
| col1 | 
+------+ 
| NULL | 
+------+ 
1 row in set (0.00 sec)
[6 Oct 2004 2:03] Miguel Solorzano
Verified against latest BK source tree.
[30 Mar 2005 22:35] 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
Note that the fix also includes that one can't anymore insert a zero date in a datetime file in traditional mode (which was another bug)