Bug #27590 Problem comparing DATEs with DATETIMEs
Submitted: 2 Apr 2007 15:08 Modified: 2 May 2007 19:41
Reporter: Dave Costantino Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.38/5.0BK OS:Linux (Linux)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: date datetime comparisons, regression

[2 Apr 2007 15:08] Dave Costantino
Description:
The behavior of DATE datatypes appears to have changed with version 5.0.38 enterprise. Previously, one could compare a DATE to a DATETIME, and the date would be treated as a DATETIME with a 00:00:00 time.

Queries of the following format do not work as expected:

select * from date_test where date >= '2007-02-10 00:00:00' and date<='2007-02-10 23:59:59';

This is affecting our applications where we depended on this behavior.

How to repeat:
Create a table with a DATE field and some sample data:

CREATE TABLE `date_test` (
  `id` int(11) NOT NULL auto_increment,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `date_test` VALUES (1,'2007-02-10'),(2,'2007-02-10'),(3,'2007-02-10'),(4,'2007-02-10'),(5,'2007-02-10'),(6,'2007-02-10'),(7,'2007-02-10'),(8,'2007-02-10'),(9,'2007-02-10'),(10,'2007-02-09'),(11,'2007-02-11');

On 5.0.30 (where this works fine):

mysql> select * from date_test where date >= '2007-02-10 00:00:00' and date<='2007-02-10 23:59:59';
+----+------------+
| id | date       |
+----+------------+
|  1 | 2007-02-10 |
|  2 | 2007-02-10 |
|  3 | 2007-02-10 |
|  4 | 2007-02-10 |
|  5 | 2007-02-10 |
|  6 | 2007-02-10 |
|  7 | 2007-02-10 |
|  8 | 2007-02-10 |
|  9 | 2007-02-10 |
+----+------------+
9 rows in set (0.02 sec)

Under 5.0.38, the behavior has changed. On 5.0.38:

mysql> select * from date_test where date >= '2007-02-10 00:00:00' and date<='2007-02-10 23:59:59';
Empty set (0.01 sec)

However, if I cast the DATE to a DATETIME, it works as before:

mysql> select * from date_test where cast(date as datetime) >= '2007-02-10 00:00:00' and cast(date as datetime)<='2007-02-10 23:59:59';
+----+------------+
| id | date       |
+----+------------+
|  1 | 2007-02-10 |
|  2 | 2007-02-10 |
|  3 | 2007-02-10 |
|  4 | 2007-02-10 |
|  5 | 2007-02-10 |
|  6 | 2007-02-10 |
|  7 | 2007-02-10 |
|  8 | 2007-02-10 |
|  9 | 2007-02-10 |
+----+------------+
9 rows in set (0.09 sec)

Suggested fix:
Restore previous behavior. Make DATEs be treated as DATETIMEs with 00:00:00 times, as in previous versions.
[2 Apr 2007 16:01] MySQL Verification Team
Thank you for the bug report.

mysql> CREATE TABLE `date_test` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `date` date NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO `date_test` VALUES
    -> (1,'2007-02-10'),(2,'2007-02-10'),(3,'2007-02-10'),(4,'2007-02-10'),
    -> (5,'2007-02-10'),(6,'2007-02-10'),(7,'2007-02-10'),(8,'2007-02-10'),
    -> (9,'2007-02-10'),(10,'2007-02-09'),(11,'2007-02-11');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from date_test where date >= '2007-02-10 00:00:00' and
    -> date<='2007-02-10 23:59:59';
Empty set (0.04 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.40-debug | 
+--------------+
1 row in set (0.01 sec)
----------------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.37-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use db1;
Database changed
mysql> CREATE TABLE `date_test` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `date` date NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> );
Query OK, 0 rows affected (0.39 sec)

mysql>
mysql> INSERT INTO `date_test` VALUES
    -> (1,'2007-02-10'),(2,'2007-02-10'),(3,'2007-02-10'),(4,'2007-02-10'),
    -> (5,'2007-02-10'),(6,'2007-02-10'),(7,'2007-02-10'),(8,'2007-02-10'),
    -> (9,'2007-02-10'),(10,'2007-02-09'),(11,'2007-02-11');
Query OK, 11 rows affected (0.08 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from date_test where date >= '2007-02-10 00:00:00' and
    -> date<='2007-02-10 23:59:59';
+----+------------+
| id | date       |
+----+------------+
|  1 | 2007-02-10 |
|  2 | 2007-02-10 |
|  3 | 2007-02-10 |
|  4 | 2007-02-10 |
|  5 | 2007-02-10 |
|  6 | 2007-02-10 |
|  7 | 2007-02-10 |
|  8 | 2007-02-10 |
|  9 | 2007-02-10 |
+----+------------+
9 rows in set (0.04 sec)

mysql>
[3 Apr 2007 20:08] Martin Friebe
An explained extended will show the diff between the 2 queries.
It will show the string as converted date value, if no time part is specified.
But if a time part is given then compare will be look at 2 strings.

This could be contradicting the doc?
 http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html
(assuming that DateTime includes Date and Time). But the doc doesn't differentiate between the different date/datetime types and how the behaviour should be in each case.

It also means that such queries will no longer will be using an index.

Comparing two strings:
 date >= '2007-02-10 00:00:00'
date will be '2007-02-10' and in string context that is less than '2007-02-10 00:00:00'

--
I believe this is a result of the fix for Bug #21103 http://lists.mysql.com/commits/21025

specific:
In sql/field.cc
 int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs)

   else
-    tmp= l_time.day + l_time.month*32 + l_time.year*16*32;
+  {
+    int3store(ptr, l_time.day + l_time.month*32 + l_time.year*16*32);
+    if(!error && (ret != MYSQL_TIMESTAMP_DATE))
+      return 2;
+  }
 
Because  '2007-02-10 00:00:00' is a DateTime it is no longer converted.
[21 Apr 2007 15:50] 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/25061

ChangeSet@1.2445, 2007-04-21 19:47:41+04:00, evgen@moonbone.local +12 -0
  Bug#27590: Wrong DATE/DATETIME comparison.
  
  DATE and DATETIME can be compared or as strings either as int. Both of
  methods have their disadvantages. Strings can contain valid DATETIME value
  but have insignificant zeros omitted thus became non-comparable with
  other DATETIME strings. The comparison as int usually will require conversion
  from the string representation and the automatic conversion in most cases do
  in a wrong way thus producing wrong comparison result. Another problem
  occurs when one tried to compare DATE field with a DATETIME constant. The 
  constant was converted to DATE loosing its precision i.e. loosing time part.
  
  This fix addresses problems described above by adding the special DATE/DATETIME
  comparator. It correctly converts DATE/DATETIME string values to int when
  it's necessary, adds zero time part (00:00:00) to DATE values to compare them
  correctly to DATETIME values. Due to correct conversion the malformed
  DATETIME string values are correctly compared to other DATE/DATETIME values.
  
  As of this patch a DATE value equals to DATETIME value with zero time part.
  For example '2001-01-01' equals to '2001-01-01 00:00:00'.
  
  The compare_datetime(), the compare_e_datetime() and the
  compare_datetime_int() functions are added to the Arg_comparator class.
  First two functions are working as wrappers to the last one which performs
  the actual comparison.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether 2 given items can be compared by means of the
  compare_datetime() comparator.
  Few caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.
[25 Apr 2007 21:19] 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/25462

ChangeSet@1.2445, 2007-04-26 01:17:16+04:00, evgen@moonbone.local +14 -0
  Bug#27590: Wrong DATE/DATETIME comparison.
  
  DATE and DATETIME can be compared or as strings either as int. Both of
  methods have their disadvantages. Strings can contain valid DATETIME value
  but have insignificant zeros omitted thus became non-comparable with
  other DATETIME strings. The comparison as int usually will require conversion
  from the string representation and the automatic conversion in most cases do
  in a wrong way thus producing wrong comparison result. Another problem
  occurs when one tried to compare DATE field with a DATETIME constant. The 
  constant was converted to DATE loosing its precision i.e. loosing time part.
  
  This fix addresses problems described above by adding the special DATE/DATETIME
  comparator. It correctly converts DATE/DATETIME string values to int when
  it's necessary, adds zero time part (00:00:00) to DATE values to compare them
  correctly to DATETIME values. Due to correct conversion the malformed
  DATETIME string values are correctly compared to other DATE/DATETIME values.
  
  As of this patch a DATE value equals to DATETIME value with zero time part.
  For example '2001-01-01' equals to '2001-01-01 00:00:00'.
  
  The compare_datetime() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether 2 given items can be compared by means of the
  compare_datetime() comparator.
  Few caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.
[26 Apr 2007 14:37] 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/25515

ChangeSet@1.2445, 2007-04-26 18:35:18+04:00, evgen@moonbone.local +14 -0
  Bug#27590: Wrong DATE/DATETIME comparison.
  
  DATE and DATETIME can be compared either as strings or as int. Both
  methods have their disadvantages. Strings can contain valid DATETIME value
  but have insignificant zeros omitted thus became non-comparable with
  other DATETIME strings. The comparison as int usually will require conversion
  from the string representation and the automatic conversion in most cases is
  carried out in a wrong way thus producing wrong comparison result. Another
  problem occurs when one tries to compare DATE field with a DATETIME constant.
  The constant is converted to DATE losing its precision i.e. losing time part.
  
  This fix addresses problems described above by adding a special DATE/DATETIME
  comparator. The comparator correctly converts DATE/DATETIME string values to
  int when it's necessary, adds zero time part (00:00:00) to DATE values to
  compare them correctly to DATETIME values. Due to correct conversion malformed
  DATETIME string values are correctly compared to other DATE/DATETIME values.
  
  As of this patch a DATE value equals to DATETIME value with zero time part.
  For example '2001-01-01' equals to '2001-01-01 00:00:00'.
  
  The compare_datetime() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether two given items can be compared by the compare_datetime()
  comparator.
  Few caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.
[26 Apr 2007 14:58] 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/25517

ChangeSet@1.2445, 2007-04-26 18:56:01+04:00, evgen@moonbone.local +14 -0
  Bug#27590: Wrong DATE/DATETIME comparison.
  
  DATE and DATETIME can be compared either as strings or as int. Both
  methods have their disadvantages. Strings can contain valid DATETIME value
  but have insignificant zeros omitted thus became non-comparable with
  other DATETIME strings. The comparison as int usually will require conversion
  from the string representation and the automatic conversion in most cases is
  carried out in a wrong way thus producing wrong comparison result. Another
  problem occurs when one tries to compare DATE field with a DATETIME constant.
  The constant is converted to DATE losing its precision i.e. losing time part.
  
  This fix addresses problems described above by adding a special DATE/DATETIME
  comparator. The comparator correctly converts DATE/DATETIME string values to
  int when it's necessary, adds zero time part (00:00:00) to DATE values to
  compare them correctly to DATETIME values. Due to correct conversion malformed
  DATETIME string values are correctly compared to other DATE/DATETIME values.
  
  As of this patch a DATE value equals to DATETIME value with zero time part.
  For example '2001-01-01' equals to '2001-01-01 00:00:00'.
  
  The compare_datetime() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether two given items can be compared by the compare_datetime()
  comparator.
  Few caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.
[26 Apr 2007 16:32] 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/25534

ChangeSet@1.2445, 2007-04-26 20:29:30+04:00, evgen@moonbone.local +14 -0
  Bug#27590: Wrong DATE/DATETIME comparison.
  
  DATE and DATETIME can be compared either as strings or as int. Both
  methods have their disadvantages. Strings can contain valid DATETIME value
  but have insignificant zeros omitted thus became non-comparable with
  other DATETIME strings. The comparison as int usually will require conversion
  from the string representation and the automatic conversion in most cases is
  carried out in a wrong way thus producing wrong comparison result. Another
  problem occurs when one tries to compare DATE field with a DATETIME constant.
  The constant is converted to DATE losing its precision i.e. losing time part.
  
  This fix addresses the problems described above by adding a special
  DATE/DATETIME comparator. The comparator correctly converts DATE/DATETIME
  string values to int when it's necessary, adds zero time part (00:00:00)
  to DATE values to compare them correctly to DATETIME values. Due to correct
  conversion malformed DATETIME string values are correctly compared to other
  DATE/DATETIME values.
  
  As of this patch a DATE value equals to DATETIME value with zero time part.
  For example '2001-01-01' equals to '2001-01-01 00:00:00'.
  
  The compare_datetime() function is added to the Arg_comparator class.
  It implements the correct comparator for DATE/DATETIME values.
  Two supplementary functions called get_date_from_str() and get_datetime_value()
  are added. The first one extracts DATE/DATETIME value from a string and the
  second one retrieves the correct DATE/DATETIME value from an item.
  The new Arg_comparator::can_compare_as_dates() function is added and used
  to check whether two given items can be compared by the compare_datetime()
  comparator.
  Few caching variables were added to the Arg_comparator class to speedup the
  DATE/DATETIME comparison.
  One more store() method was added to the Item_cache_int class to cache int
  values.
  The new is_datetime() function was added to the Item class. It indicates
  whether the item returns a DATE/DATETIME value.
[27 Apr 2007 20:41] 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/25633

ChangeSet@1.2444, 2007-04-28 00:04:50+04:00, evgen@sunlight.local +4 -0
  Additional fix for the bug#27590.
[30 Apr 2007 12:58] Bugs System
Pushed into 5.0.42
[30 Apr 2007 12:59] Bugs System
Pushed into 5.1.18-beta
[1 May 2007 11:02] Bugs System
Pushed into 5.1.18-beta
[1 May 2007 11:03] Bugs System
Pushed into 5.0.42
[2 May 2007 19:41] Paul DuBois
Noted in 5.0.42, 5.1.18 changelogs.

Comparison of a DATE with a DATETIME did not treat the DATE as having
a time part of 00:00:00.
[6 May 2008 16:27] Paul DuBois
See also Bug#28929.