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

[6 Nov 2007 21:38] Peter Brawley
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)
[6 Nov 2007 21:40] Peter Brawley
I neglected to mention that the bug occurs with the mysql client, but not via PHP. I did not test other connectors.
[7 Nov 2007 4:21] Valeriy Kravchuk
Looks like a duplicate of bug #31709. Please, check.
[7 Nov 2007 4:35] Peter Brawley
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.
[7 Nov 2007 9:57] Valeriy Kravchuk
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.
[7 Nov 2007 10:04] Valeriy Kravchuk
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.