Description of problem:

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.


Version-Release number of selected component (if applicable):
5.0.45

How reproducible:

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


Additional info:
MySQL resolved this in build 5.0.51a.