Bug #21103 DATE column not compared as DATE
Submitted: 18 Jul 2006 1:32 Modified: 4 Jun 2007 18:38
Reporter: Travers Carter Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.21, 5.1.15-BK OS:Linux (Linux)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[18 Jul 2006 1:32] Travers Carter
Description:
When comparing a DATE column to NOW() today's date (CURDATE()) is not less than NOW() but when it is cast to DATE it is.

SELECT CURDATE() < NOW(), Val < NOW(), CAST(Val AS DATE) < NOW() FROM datetest;

This behaviour is evident in both 5.0.21 and 4.1.20-Max

Actual Result:
+-------------------+-------------+---------------------------+
| CURDATE() < NOW() | Val < NOW() | CAST(Val AS DATE) < NOW() |
+-------------------+-------------+---------------------------+
|                 1 |           0 |                         1 |
+-------------------+-------------+---------------------------+

Expected result (Val is less than NOW()):
+-------------------+-------------+---------------------------+
| CURDATE() < NOW() | Val < NOW() | CAST(Val AS DATE) < NOW() |
+-------------------+-------------+---------------------------+
|                 1 |           1 |                         1 |
+-------------------+-------------+---------------------------+

How to repeat:
CREATE TABLE datetest(Val DATE NOT NULL);
INSERT INTO datetest VALUES (CURDATE());
SELECT CURDATE() < NOW(), Val < NOW(), CAST(Val AS DATE) < NOW() FROM datetest;
[18 Jul 2006 6:34] Hartmut Holzgraefe
Looks like a conversion bug as "val <= NOW()" returns 1,
so in this comparison both values seem to be treated as 
DATE values, with NOW() being converted from DATETIME to
DATE whereas in "CAST(Val AS DATE) < NOW()" the CAST
result is further converted to a DATETIME

I'm not sure whether these differences in type conversion
are really expected behavior so i'm setting this to 
"verified" for now
[9 Nov 2006 0:47] Matthew Montgomery
Here's another example where the automatic type conversion isn't behaving as expected for DATE;

mysql> create table test (field DATE);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into test values ('2006-11-06');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test where field < '2006-11-06 04:08:36.0'; 
Empty set (0.00 sec) # 
 
mysql> select * from test where CAST(field as DATE) < '2006-11-06 04:08:36.0';
+------------+
| field      |
+------------+
| 2006-11-06 |
+------------+
1 row in set (0.00 sec)
[11 Dec 2006 22:30] 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/16803

ChangeSet@1.2348, 2006-12-11 23:31:34+01:00, tnurnberg@mysql.com +9 -0
  Bug #21103: DATE column not compared as DATE
  
  If we compare two items A and B, with B being (a constant) of a larger type,
  then A gets promoted to B's type for comparison if it's a constant, function,
  or CAST() column, but B gets demoted to A's type if A is a (not explicitly CAST())
  column. This is counter-intuitive and not mandated by the standard.
   
  Disabling optimisation where it would be lossy so field value will properly get
  promoted and compared as binary string (rather than as integers).
[3 Feb 2007 9:08] Valeriy Kravchuk
This should be fixed in 5.1 also. In 5.1.15-BK we still have:

openxs@suse:~/dbs/5.1> 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.15-beta Source distribution

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

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (field DATE);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values ('2006-11-06');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where field < '2006-11-06 04:08:36.0';
Empty set (0.00 sec)

mysql> select * from test where CAST(field as DATE) < '2006-11-06 04:08:36.0';
+------------+
| field      |
+------------+
| 2006-11-06 |
+------------+
1 row in set (0.01 sec)
[2 Mar 2007 14:24] 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/21025

ChangeSet@1.2457, 2007-03-02 15:23:13+01:00, tnurnberg@mysql.com +9 -0
  Bug #21103: DATE column not compared as DATE
  
  If we compare two items A and B, with B being (a constant) of a
  larger type, then A gets promoted to B's type for comparison if
  it's a constant, function, or CAST() column, but B gets demoted
  to A's type if A is a (not explicitly CAST()) column. This is
  counter-intuitive and not mandated by the standard.
   
  Disabling optimisation where it would be lossy so field value
  will properly get promoted and compared as binary string (rather
  than as integers).
[5 Mar 2007 10:46] 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/21137

ChangeSet@1.2462, 2007-03-05 11:13:39+01:00, tnurnberg@mysql.com +1 -0
  Bug#21103: DATE column not compared as DATE
  
  When comparing a DATE field with a DATETIME constant, we now compare
  as DATETIMEs, not as DATEs.  Fix BDB queries to still work.
[5 Mar 2007 12:44] 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/21141

ChangeSet@1.2396, 2007-03-05 13:21:35+01:00, tnurnberg@mysql.com +1 -0
  Bug#21103: DATE column not compared as DATE
  
  When comparing a DATE field with a DATETIME constant, we now compare
  as DATETIMEs, not as DATEs.  Fix BDB queries to still work.
[5 Mar 2007 13:03] 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/21143

ChangeSet@1.2458, 2007-03-05 14:02:29+01:00, tnurnberg@mysql.com +1 -0
  Bug#21103: DATE column not compared as DATE
  
  When comparing a DATE field with a DATETIME constant, we now compare
  as DATETIMEs, not as DATEs.  Fix BDB queries to still work.
[8 Mar 2007 22:12] Timothy Smith
pushed to 5.0.38, 5.1.17
[16 Mar 2007 16:48] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.

CURDATE() is less than NOW(), either when comparing CURDATE()
directly (CURDATE() < NOW() is true) or when casting CURDATE() to
DATE (CAST(CURDATE() AS DATE) < NOW() is true). However, storing
CURDATE() in a DATE column and comparing col_name < NOW() incorrectly
yielded false. This is fixed by comparing a DATE column as DATETIME
for comparisons to a DATETIME constant.
[23 Mar 2007 20:07] 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/22832

ChangeSet@1.2504, 2007-03-23 22:08:31+02:00, monty@mysql.com +44 -0
  Removed not used define YY_MAGIC_BELOW
  Made year 2000 handling more uniform
  Removed year 2000 handling out from calc_days()
  The above removes some bugs in date/datetimes with year between 0 and 200
  Now we get a note when we insert a datetime value into a date column
  For default values to CREATE, don't give errors for warning level NOTE
  Fixed some compiler failures
  Added library ws2_32 for windows compilation (needed if we want to compile with IOCP support)
  Removed duplicate typedef TIME and replaced it with MYSQL_TIME
  
  Better (more complete) fix for: Bug#21103 "DATE column not compared as DATE"
  Fixed properly Bug#18997 "DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year value"
  Fixed Bug#23093 "Implicit conversion of 9912101 to date does not match cast(9912101 as date)"
[10 Apr 2007 15:40] Tatiana Azundris Nuernberg
queued in
5.0.40-marvel
5.1.17-marvel
[18 Apr 2007 15:46] Bugs System
Pushed into 5.1.18-beta
[24 Apr 2007 1:24] Paul Dubois
Moved the 5.1.17 changelog to 5.1.18.

Setting report to Patch Queued pending
merge into 5.0.x.
[14 May 2007 9:29] 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/26579

ChangeSet@1.2478, 2007-05-14 11:28:59+02:00, tnurnberg@blasphemy.mysql.com +37 -0
  Backport of TIME->MYSQL_TIME / Y2K fixset
  
  Made year 2000 handling more uniform
  Removed year 2000 handling out from calc_days()
  The above removes some bugs in date/datetimes with year between 0 and 200
  Now we get a note when we insert a datetime value into a date column
  For default values to CREATE, don't give errors for warning level NOTE
  Fixed some compiler failures
  Added library ws2_32 for windows compilation (needed if we want to compile with IOCP support)
  Removed duplicate typedef TIME and replaced it with MYSQL_TIME
  
  Better (more complete) fix for: Bug#21103 "DATE column not compared as DATE"
  Fixed properly Bug#18997 "DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year value"
  Fixed Bug#23093 "Implicit conversion of 9912101 to date does not match cast(9912101 as date)"
[14 May 2007 11:03] 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/26584

ChangeSet@1.2478, 2007-05-14 13:02:47+02:00, tnurnberg@blasphemy.mysql.com +38 -0
  Backport of TIME->MYSQL_TIME / Y2K fixset
  
  Made year 2000 handling more uniform
  Removed year 2000 handling out from calc_days()
  The above removes some bugs in date/datetimes with year between 0 and 200
  Now we get a note when we insert a datetime value into a date column
  For default values to CREATE, don't give errors for warning level NOTE
  Fixed some compiler failures
  Added library ws2_32 for windows compilation (needed if we want to compile with IOCP support)
  Removed duplicate typedef TIME and replaced it with MYSQL_TIME
  
  Better (more complete) fix for: Bug#21103 "DATE column not compared as DATE"
  Fixed properly Bug#18997 "DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year value"
  Fixed Bug#23093 "Implicit conversion of 9912101 to date does not match cast(9912101 as date)"
[14 May 2007 12:53] Tatiana Azundris Nuernberg
pushed to 5.0.42-maint
[14 May 2007 15:44] 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/26622

ChangeSet@1.2480, 2007-05-14 17:44:36+02:00, tnurnberg@blasphemy.mysql.com +1 -0
  Bug#21103: "DATE column not compared as DATE"
  BDB results fixed (not p/o 5.1 fix)
[16 May 2007 8:45] 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/26781

ChangeSet@1.2481, 2007-05-16 10:44:59+02:00, msvensson@pilot.blaudden +38 -0
  Backport of TIME->MYSQL_TIME / Y2K fixset
     
  Made year 2000 handling more uniform
  Removed year 2000 handling out from calc_days()
  The above removes some bugs in date/datetimes with year between 0 and 200
  Now we get a note when we insert a datetime value into a date column
  For default values to CREATE, don't give errors for warning level NOTE
  Fixed some compiler failures
  Added library ws2_32 for windows compilation (needed if we want to compile with IOCP support)
  Removed duplicate typedef TIME and replaced it with MYSQL_TIME
  
  Better (more complete) fix for: Bug#21103 "DATE column not compared as DATE"
  Fixed properly Bug#18997 "DATE_ADD and DATE_SUB perform year2K autoconversion magic on 4-digit year value"
  Fixed Bug#23093 "Implicit conversion of 9912101 to date does not match cast(9912101 as date)"
[16 May 2007 12:00] 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/26820

ChangeSet@1.2483, 2007-05-16 14:00:47+02:00, msvensson@pilot.blaudden +1 -0
  Bug#21103: "DATE column not compared as DATE"
  BDB results fixed (not p/o 5.1 fix)
[22 May 2007 17:00] Bugs System
Pushed into 5.1.19-beta
[22 May 2007 17:02] Bugs System
Pushed into 5.0.44
[4 Jun 2007 18:38] Paul Dubois
Moved 5.0 changelog entry from 5.0.38 to 5.0.44.
Moved 5.1 changelog entry from 5.1.18 to 5.1.19.