| Bug #6145 | Traditional: CONVERT and CAST should reject zero DATE values | ||
|---|---|---|---|
| Submitted: | 18 Oct 2004 19:10 | Modified: | 4 Apr 2005 15:11 |
| 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 91.) |
| Assigned to: | Michael Widenius | CPU Architecture: | Any |
[19 Oct 2004 0:34]
MySQL Verification Team
Verified with latest BK source.
[4 Apr 2005 15:11]
Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Fix will be in 5.0.4
Description: When sql_mode='traditional', all DATE/DATETIME/TIMESTAMP values that are not valid calendar values must be rejected. But for DATE values, MySQL accepts an explicit CONVERT of a zero date, and an explicit CAST of a zero date, silently changing the values to NULL. Traditional mode works correctly with DATETIME and TIMESTAMP values in thee cases. 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.01 sec) mysql> insert into t1 (col1) values ('0000-00-00'); ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'col1' at row 1 -- This is the correct response. mysql> insert into t1 (col1) values (convert('0000-00-00',date)); Query OK, 1 row affected (0.00 sec) -- This is the incorrect response. The INSERT should also be rejected, with SQLSTATE 22007. mysql> insert into t1 (col1) values (cast('0000-00-00' as date)); Query OK, 1 row affected (0.00 sec) -- This is the incorrect response. The INSERT should also be rejected, with SQLSTATE 22007. mysql> select * from t1; +------+ | col1 | +------+ | NULL | | NULL | +------+ -- Table t1 should have zero rows.