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:
None 
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

[5 Oct 2004 18:08] Trudy Pelzer
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)
[6 Oct 2004 0: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)
[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.