Bug #28869 Broken DATE and DATETIME comparisons
Submitted: 4 Jun 2007 8:40 Modified: 5 Jun 2007 14:45
Reporter: Domas Mituzas Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: regression

[4 Jun 2007 8:40] Domas Mituzas 
Description:
Datetime and Date comparison operations are broken:

How to repeat:
mysql> select 
'2007-06-30' + interval 1 hour < cast('2007-07-05' as date), 
'2007-06-30' + interval 1 hour < cast('2007-07-05' as date), 
'2007-06-30' + interval 1 hour <= cast('2007-07-05' as date), 
'2007-06-30' + interval 1 hour <= cast('2007-07-05' as datetime), 
'2007-06-30' + interval 1 hour = cast('2007-07-05' as datetime), 
'2007-06-30' + interval 1 hour = cast('2007-07-05' as date) \G

*************************** 1. row ***************************
     '2007-06-30' + interval 1 hour < cast('2007-07-05' as date): 0
     '2007-06-30' + interval 1 hour < cast('2007-07-05' as date): 0
    '2007-06-30' + interval 1 hour <= cast('2007-07-05' as date): 1
'2007-06-30' + interval 1 hour <= cast('2007-07-05' as datetime): 1
'2007-06-30' + interval 1 hour = cast('2007-07-05' as datetime): 1
     '2007-06-30' + interval 1 hour = cast('2007-07-05' as date): 1
1 row in set (0.00 sec)

Suggested fix:
This behavior could have been introduced by:

* Comparison of a DATE with a DATETIME did not treat the DATE as having a time part of 00:00:00. (Bug#27590)
* Comparisons of DATE or DATETIME values for the IN() function could yield incorrect results. (Bug#28133)

(both in 5.0.40)

It is a REGRESSION that appeared in 5.0.42
[4 Jun 2007 14:16] Geert Vanderkelen 
Other test case: Works fine in 5.0.41, but breaks in 5.0.42:

CREATE TABLE `t1` (
  `today` date default NULL
);

mysql> INSERT INTO t1 VALUES (NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1 WHERE today = NOW();
Empty set (0.01 sec)

mysql> SELECT * FROM t1 WHERE today = CURDATE();
+------------+
| today      |
+------------+
| 2007-06-04 | 
+------------+
[5 Jun 2007 13:30] Evgeny Potemkin 
mysql> select 
    -> '2007-06-30' + interval 1 hour < cast('2007-07-05' as date), 
    -> '2007-06-30' + interval 1 hour < cast('2007-07-05' as date), 
    -> '2007-06-30' + interval 1 hour <= cast('2007-07-05' as date), 
    -> '2007-06-30' + interval 1 hour <= cast('2007-07-05' as datetime), 
    -> '2007-06-30' + interval 1 hour = cast('2007-07-05' as datetime), 
    -> '2007-06-30' + interval 1 hour = cast('2007-07-05' as date) \G
*************************** 1. row ***************************
     '2007-06-30' + interval 1 hour < cast('2007-07-05' as date): 1
     '2007-06-30' + interval 1 hour < cast('2007-07-05' as date): 1
    '2007-06-30' + interval 1 hour <= cast('2007-07-05' as date): 1
'2007-06-30' + interval 1 hour <= cast('2007-07-05' as datetime): 1
 '2007-06-30' + interval 1 hour = cast('2007-07-05' as datetime): 0
     '2007-06-30' + interval 1 hour = cast('2007-07-05' as date): 0
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.44-debug | 
+--------------+
1 row in set (0.00 sec)
[5 Jun 2007 13:54] Evgeny Potemkin 
CREATE TABLE `t1` (`today` date default NULL);
mysql> INSERT INTO t1 VALUES (NOW());
mysql> SELECT * FROM t1 WHERE today = NOW();
Empty set (0.01 sec)

This is expected behaviour. You save DATETIME value in a DATE field and thus
the TIME part gets stripped. DATE is treated as a DATETIME with zero TIME part i.e. DATE 00:00:00. NOW() returns DATETIME value with non-zero TIME part and obviously it's greater than the same DATE with zero TIME part.

mysql> SELECT * FROM t1 WHERE today = CURDATE();
[1 row]

It's correct as values are the same.
[5 Jun 2007 14:45] Domas Mituzas 
Apparently this bug is a duplicate of #21103
[6 Jun 2007 16:07] Axel Schwenke 
with latest bk (2007-06-06 18:00:00) I get now:

mysql> select 
    -> '2007-06-30' + interval 1 hour < cast('2007-07-05' as date), 
    -> '2007-06-30' + interval 1 hour < cast('2007-07-05' as date), 
    -> '2007-06-30' + interval 1 hour <= cast('2007-07-05' as date), 
    -> '2007-06-30' + interval 1 hour <= cast('2007-07-05' as datetime), 
    -> '2007-06-30' + interval 1 hour = cast('2007-07-05' as datetime), 
    -> '2007-06-30' + interval 1 hour = cast('2007-07-05' as date) \G
*************************** 1. row ***************************
     '2007-06-30' + interval 1 hour < cast('2007-07-05' as date): 1
     '2007-06-30' + interval 1 hour < cast('2007-07-05' as date): 1
    '2007-06-30' + interval 1 hour <= cast('2007-07-05' as date): 1
'2007-06-30' + interval 1 hour <= cast('2007-07-05' as datetime): 1
 '2007-06-30' + interval 1 hour = cast('2007-07-05' as datetime): 0
     '2007-06-30' + interval 1 hour = cast('2007-07-05' as date): 0
1 row in set (0,00 sec)
[6 Jun 2007 16:17] Axel Schwenke 
However there is still the problem of comparing DATE columns with DATETIME literals:

mysql> create table t1 (c1 date); insert into t1 values (now());
Query OK, 0 rows affected (0,01 sec)

Query OK, 1 row affected (0,00 sec)

mysql> select * from t1 where c1=now();
Empty set (0,00 sec)

mysql> select * from t1 where c1=cast(now() as date);
+------------+
| c1         |
+------------+
| 2007-06-06 | 
+------------+
1 row in set (0,02 sec)