Bug #50293 Difference in SELECT output when search is based on an invalid DATE/DATETIME

• Submitted: 12 Jan 2010 22:23
• Modified: 14 Jan 2010 23:41
• Reporter: Nirbhay Choubey
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1-bugteam, 5.5.99
• OS: Any
• CPU Architecture: Any

[12 Jan 2010 22:23] Nirbhay Choubey

Description:
Difference between the SELECT output is noticed when the table with DATETIME or DATE attribute, containing '0000-00-00 00:00:00' or '0000-00-00', is searched using and invalid value.

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 DATETIME);
INSERT INTO t1 VALUES ('0000-00-00 00:00:00');

for 5.1-bugteam build:
SELECT * FROM t1 WHERE c1='2001-01-09 24:59:59'; /*Incorrect value*/
c1
0000-00-00 00:00:00

(this might be due to the fact the incorrect values are internally converted into '0000-00-00 00:00:00')

for 5.1-main build:
SELECT * FROM t1 WHERE c1='2001-01-09 24:59:59'; /*Incorrect value*/
Empty set

How to repeat:
See the above description.

[13 Jan 2010 6:35] Sveta Smirnova

Thank you for the report.

Verified as described.

[14 Jan 2010 23:41] Omer Barnir

This change is expected and is part of recent fixes.
MySQL converts invalid dates to '0000-00-00 00:00:00' and then interprets them so the invalid value inserted was inserted as '0000-00-00 00:00:00' and the invalid value in the where clause was converted to '0000-00-00 00:00:00' so they match.

The same happens for integer columns. If you try to insert 'abc' it will be inserted as zero and if you then query for WHERE col='string' the zero rows will be returned.