| Bug #59173 | Failure to handle DATE(TIME) values where Year, Month or Day is ZERO | ||
|---|---|---|---|
| Submitted: | 26 Dec 2010 12:26 | Modified: | 29 Jan 2011 23:07 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1.54, 5.1.55, 5.5.8 | OS: | Any |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | qc, regression | ||
[26 Dec 2010 12:45]
Valeriy Kravchuk
Thank you for the problem report. I also do not see any good reason to accept values that can not be easily updated or deleted. Verified with current mysql-5.1:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.55-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET SQL_MODE = '';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> DROP TABLE IF EXISTS dttest;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE TABLE `dttest` (`dt1` DATETIME);
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> INSERT INTO `dttest` (`dt1`) VALUES ('0000-00-01 00:00:01');
Query OK, 1 row affected (0.00 sec)
mysql> select * from dttest;
+---------------------+
| dt1 |
+---------------------+
| 0000-00-01 00:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> DELETE FROM `dttest` WHERE `dt1`='0000-00-01 00:00:01';
Query OK, 0 rows affected (0.00 sec)
mysql> select * FROM `dttest` WHERE `dt1`='0000-00-01 00:00:01';
+---------------------+
| dt1 |
+---------------------+
| 0000-00-01 00:00:01 |
+---------------------+
1 row in set (0.00 sec)
[26 Dec 2010 13:26]
Peter Laursen
Same problem in 5.5.8. In 5.0.90 the DELETE statement deletes the row as expected.
[26 Dec 2010 13:31]
Peter Laursen
Fixed typo in synopsis and added 'regression' as TAG.
[26 Dec 2010 16:46]
Peter Laursen
However if the DATE is '0000-00-00' the DELETE statement works. So setting to S3 - even though I had to 1) copy affected table to a 5.0 server 2) perform DELETES on 5.0 3) TRUNCATE TABLE on 5.1 4) copy table 5.0 > 5.1 Setting DATE-values to things like '0000-00-01' is a trick that I have used for some time when inserting values while testing an application. I could then easily identify and delete the garbage values inserted during testing. But with 5.1 and 5.5 server they fail to delete.
[27 Dec 2010 13:20]
Peter Laursen
It seems it also DELETES if there is an index on the DATETIME column.
[10 Jan 2011 12: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/commits/128282 3539 Martin Hansson 2011-01-10 Bug#59173: Failure to handle DATE(TIME) values where Year, Month or Day is ZERO When dates are represented internally as strings, i.e. when a string constant is compared to a date value, both values are converted to long integers, ostensibly for fast comparisons. DATE typed integer values are converted to DATETIME by multiplying by 1,000,000 (each digit pair representing hour, minute and second, respectively). But the mechanism did not distuinguish cached INTEGER values, already in correct format, from newly converted strings. Fixed by not multiplying the values for cached integer values.
[18 Jan 2011 7:48]
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/commits/129033 3539 Martin Hansson 2011-01-18 Bug#59173: Failure to handle DATE(TIME) values where Year, Month or Day is ZERO When dates are represented internally as strings, i.e. when a string constant is compared to a date value, both values are converted to long integers, ostensibly for fast comparisons. DATE typed integer values are converted to DATETIME by multiplying by 1,000,000 (each digit pair representing hour, minute and second, respectively). But the mechanism did not distuinguish cached INTEGER values, already in correct format, from newly converted strings. Fixed by marking the INTEGER cache as being of DATETIME format.
[19 Jan 2011 14:09]
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/commits/129198 3562 Martin Hansson 2011-01-19 Bug#59173: Failure to handle DATE(TIME) values where Year, Month or Day is ZERO When dates are represented internally as strings, i.e. when a string constant is compared to a date value, both values are converted to long integers, ostensibly for fast comparisons. DATE typed integer values are converted to DATETIME by multiplying by 1,000,000 (each digit pair representing hour, minute and second, respectively). But the mechanism did not distuinguish cached INTEGER values, already in correct format, from newly converted strings. Fixed by marking the INTEGER cache as being of DATETIME format.
[19 Jan 2011 14:13]
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/commits/129201 3268 Martin Hansson 2011-01-19 [merge] Merge of fix for Bug#59173.
[19 Jan 2011 14:40]
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/commits/129205 3535 Martin Hansson 2011-01-19 [merge] Merge of fix for Bug#59173.
[20 Jan 2011 8:09]
Bugs System
Pushed into mysql-trunk 5.6.2 (revid:martin.hansson@oracle.com-20110119143913-7ctrb0tgk43csqwa) (version source revid:martin.hansson@oracle.com-20110119143913-7ctrb0tgk43csqwa) (merge vers: 5.6.2) (pib:24)
[20 Jan 2011 8:21]
Bugs System
Pushed into mysql-5.5 5.5.10 (revid:martin.hansson@oracle.com-20110120081945-daabnuovwm7bi1s6) (version source revid:martin.hansson@oracle.com-20110119141243-lf8t22de2uo8tlpy) (merge vers: 5.5.10) (pib:24)
[20 Jan 2011 8:29]
Bugs System
Pushed into mysql-5.1 5.1.56 (revid:martin.hansson@oracle.com-20110120082517-h4k1n2nncwf47cks) (version source revid:martin.hansson@oracle.com-20110119140932-lhj3epftxw59f9vg) (merge vers: 5.1.56) (pib:24)
[20 Jan 2011 19:08]
Paul DuBois
Noted in 5.1.56, 5.5.10, 5.6.2 changelogs. DELETE or UPDATE statements could fail if they used DATE or DATETIME values with a year, month, or day part of zero.
Description: I have for some times been plagued that DATE/DATETIME values fail to react (DELETE, UPDATE) properly if either Year, Month or Day is ZERO. They INSERT OK, but do not UPDATE or DELETE. Such values are valid (depending on SQL_mode). How to repeat: SELECT VERSION(); -- 5.1.54-community SET SQL_MODE = ''; DROP TABLE IF EXISTS dttest; CREATE TABLE `dttest` (`dt1` DATETIME); INSERT INTO `dttest` (`dt1`) VALUES ('0000-00-01 00:00:01'); -- 1 row(s) affected DELETE FROM `dttest` WHERE `dt1`='0000-00-01 00:00:01'; -- 0 row(s) affected Suggested fix: If such value can INSERT, it should also DELETE/UPDATE.