Bug #10627 Traditional: invalid date accepted from DATE_ADD/DATE_SUB functions
Submitted: 13 May 2005 16:38 Modified: 17 Aug 2005 17:29
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.6-beta-debug OS:Linux (SuSE 9.1)
Assigned to: Jim Winstead CPU Architecture:Any

[13 May 2005 16:38] Trudy Pelzer
Description:
When sql_mode='traditional', all invalid DATE 
values must be rejected. But the server converts
an invalid result from the DATE_ADD and DATE_SUB
functions to NULL.

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.06 sec)

mysql> insert into t1 select date_sub('2000-01-01',interval 2001 year);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
-- This is the incorrect response. Since the calculation results
in an invalid date, the INSERT should fail with 
SQLSTATE 22007 incorrect date value ...

mysql> select * from t1;
+------------+
| col1           |
+------------+
| NULL         |
+------------+
1 row in set (0.00 sec)

mysql> insert into t1 select date_add('2000-01-01',interval 8000 year);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
-- This is the incorrect response. Since the calculation results
in an invalid date, the INSERT should fail with 
SQLSTATE 22007 incorrect date value ...

mysql> select * from t1;
+------------+
| col1       |
+------------+
| NULL      |
| NULL       |
+------------+
2 rows in set (0.00 sec)
[13 May 2005 16:53] MySQL Verification Team
Thank you for the bug report.
[27 Jul 2005 21:43] 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/27661
[2 Aug 2005 22:27] 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/27825
[15 Aug 2005 22:25] Jim Winstead
Fixed in 5.0.12.
[17 Aug 2005 17:29] Paul Dubois
Noted in 5.0.12 changelog.