| 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: | |
| 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 2:03]
MySQL Verification Team
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)
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)