| Bug #5906 | Traditional mode: invalid date due to conversion accepted | ||
|---|---|---|---|
| Submitted: | 5 Oct 2004 18:08 | Modified: | 20 Jul 2005 14:46 |
| 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: | Jim Winstead | CPU Architecture: | Any |
[6 Oct 2004 0:35]
MySQL Verification Team
Verified against latest BK source tree. However the last action wasn't confirmed: mysql> select * from t1; +------------+ | col1 | +------------+ | 2000-10-00 | +------------+ 1 row in set (0.00 sec) mysql> update t1 set col1=1100; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from t1; +------------+ | col1 | +------------+ | 2000-10-00 | +------------+ 1 row in set (0.00 sec)
[5 Jul 2005 22:49]
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/26694
[14 Jul 2005 17:06]
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/27090
[20 Jul 2005 0:58]
Jim Winstead
Fixed in 5.0.10.
[20 Jul 2005 14:46]
Mike Hillyer
Documented in 5.0.10 changelog.
Description: When sql_mode='traditional', invalid date values must be rejected. But if the invalid value is the result of an implicit data type conversion, the server is accepting it. That is: CREATE TABLE t1 (col1 DATE); INSERT INTO t1 VALUES (1000); inserts the value '2000-10-00', rather than rejecting the INSERT with SQLSTATE 22007: Incorrect date value: ... UPDATE t1 SET col1=1100; updates the value to '2000-11-00', rather than rejecting the UPDATE with SQLSTATE 22007: Incorrect date value: ... How to repeat: mysql> set sql_mode='traditional'; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (col1 date); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values('2000-10-00'); ERROR 1292 (22007): Incorrect date value: '2000-10-00' 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(1000); Query OK, 1 row affected (0.00 sec) -- This is the incorrect response; the INSERT should be rejected. mysql> select * from t1; +------------+ | col1 | +------------+ | 2000-10-00 | +------------+ 1 row in set (0.01 sec) mysql> update t1 set col1=1100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- This is the incorrect response; the UPDATE should be rejected. mysql> select * from t1; +------------+ | col1 | +------------+ | 2000-11-00 | +------------+ 1 row in set (0.00 sec)