MySQL Bugs: #21103: DATE column not compared as DATE

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

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;

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

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)

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).

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)

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).

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.

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.

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.

pushed to 5.0.38, 5.1.17

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.

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)"

queued in
5.0.40-marvel
5.1.17-marvel

Pushed into 5.1.18-beta

Moved the 5.1.17 changelog to 5.1.18.

Setting report to Patch Queued pending
merge into 5.0.x.

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)"

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)"

pushed to 5.0.42-maint

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)

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)"

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)

Pushed into 5.1.19-beta

Pushed into 5.0.44

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.