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:
None 
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
Triage: Triaged: D2 (Serious)

[26 Dec 2010 12:26] Peter Laursen
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.
[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.