| Bug #5906 | Traditional mode: invalid date due to conversion accepted | ||
|---|---|---|---|
| Submitted: | 5 Oct 2004 20:08 | Modified: | 20 Jul 2005 16:46 |
| Reporter: | Trudy Pelzer | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2-alpha-debug | OS: | Linux (SuSE 9.1) |
| Assigned to: | Jim Winstead | Target Version: | |
[6 Oct 2004 2:35]
Miguel Solorzano
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)
[6 Jul 2005 0: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 19: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 2:58]
Jim Winstead
Fixed in 5.0.10.
[20 Jul 2005 16: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)