| 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: | |
| 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: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.
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)