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