MySQL Bugs: #32152: Null datetime value makes datetime values in succeeding rows disappear

Bug #32152	Null datetime value makes datetime values in succeeding rows disappear
Submitted:	6 Nov 2007 21:38	Modified:	7 Nov 2007 9:57
Reporter:	Peter Brawley (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
In 5.045, but not in either 5.0.37 or 5.1.22, if a row has a NULL datetime value, datetime values of that column in succeeding rows are not retrieved. 

How to repeat:
USE test;
DROP TABLE IF EXISTS t;
CREATE TABLE t (
  ID int(11) NOT NULL auto_increment,
  dt datetime default NULL,
 PRIMARY KEY (ID)
);
INSERT INTO t VALUES (1,NULL),(2,now()),(3,now());
SELECT * FROM t WHERE DATE(dt)=current_date;

-- returns: Empty set (0.00 sec)

-- Oddly, trimming the return value of DATE() yields the correct result:
SELECT * FROM t WHERE TRIM(DATE(dt))=current_date;
+----+---------------------+
| ID | dt                  |
+----+---------------------+
|  2 | 2007-11-06 14:38:00 |
|  3 | 2007-11-06 14:38:00 |
+----+---------------------+
2 rows in set (0.00 sec)

I neglected to mention that the bug occurs with the mysql client, but not via PHP. I did not test other connectors.

Looks like a duplicate of bug #31709. Please, check.

Bugs 31709 and 31933 report the same phenomenon, though not the workaround.

A search for 'date,null' in bugs failed to turn up either 31709 or 31933. Fixing the bug-search module would save everyone a lot of time.

So, this is a duplicate of bug #31709. Please, add your workaround there, if you think it is appropriate. I'll try to escalate bug #31709.

Sorry, no need to escalate for now. Let's wait for next Community release, 5.0.51, and check if the bug will not be repeatable there.