MySQL Bugs: #29898: DATE() function returns inconsistent results

Bug #29898	DATE() function returns inconsistent results
Submitted:	19 Jul 2007 14:22	Modified:	24 Jul 2007 16:08
Reporter:	Rob Morris	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.42 and up	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	backport_050044SP1, regression

Description:
Using the DATE() function in the where clause does not return any records after a null is encountered.  It will work if you use TRIM or CAST on the results from the DATE function.

How to repeat:
CREATE TABLE Table1 
(                                   
  id tinyint(3)   NOT NULL      AUTO_INCREMENT,
  d1 datetime     NOT NULL,
  d2 datetime     DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO Table1(d1, d2)
VALUES ('2007-07-19 08:30:00', '2007-07-19 08:30:00'),
('2007-07-19 08:31:00', '2007-07-19 08:31:00'),
('2007-07-19 08:32:00', '2007-07-19 08:32:00'),
('2007-07-19 08:33:00', NULL),
('2007-07-19 08:34:00', '2007-07-19 08:34:00'),
('2007-07-19 08:35:00', NULL),
('2007-07-19 08:36:00', '2007-07-19 08:36:00'),
('2007-07-19 08:37:00', '2007-07-19 08:37:00'),
('2007-07-19 08:38:00', NULL),
('2007-07-19 08:39:00', '2007-07-19 08:39:00');

SELECT *
FROM Table1
WHERE DATE(d1) = '2007-07-19';
/* returns all 10 records */

SELECT *
FROM Table1
WHERE DATE(d2) = '2007-07-19';
/* returns only 3 records */

SELECT *
FROM Table1
WHERE trim(DATE(d2)) = '2007-07-19';
/* returns all 7 records */

SELECT *
FROM Table1
WHERE CAST(DATE(d2) AS char(10)) = '2007-07-19';
/* returns all 7 records */

works fine on 5.0.38, fails on 5.0.42 and 5.0.44,
now attaching mysqltest test case and testing 5.0.40 ...

regression seem to have started with 5.0.42

mysqltest test case

Attachment: bug29899.tgz (application/x-gtar, text), 779 bytes.

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/31202

ChangeSet@1.2539, 2007-07-20 00:06:35+04:00, evgen@moonbone.local +3 -0
  Bug#29898: Item_date_typecast::val_int doesn't reset the null_value flag.
  
  The Item_date_typecast::val_int function doesn't reset null_value flag.
  This makes all values that follows the first null value to be treated as nulls
  and led to a wrong result.
  
  Now the Item_date_typecast::val_int function correctly sets the null_value flag
  for both null and non-null values.

This isn't a regression.
Prior to 5.0.42 DATE values were compares as strings 
and this bug didn't occur. Current DATE/DATETIME comparator compares them as ints thus the bug manifested itself.

Pushed into 5.1.21-beta

Pushed into 5.0.48

Bug #30002 was marked as duplicate of this one.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Added to the changelogs for version 5.0 and 5.1.

Bug #35083 was marked as duplicate of this one.

Ran into this bug yesterday on a RHEL5 system running MySQL 5.0.45.  SELECT * FROM table WHERE DATE(datetimefield) stopped returning results unexpectedly.  The same query works fine on a second identical system (RHEL5, MySQL 5.0.45).