Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micro

Reported by Philip Stoev on 2011-03-16
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Maria
Fix Released
High
Sergei

Bug Description

Not repeatable with mysql-5.1. Using a datetime function in a subquery context causes wrong results to be returned and the following warning to be printed out:

Warning 1292 Incorrect datetime value: '72294422400000000' for column '<left expr>' at row 1

test case:

CREATE TABLE t1 (f1 INTEGER, f2 DATE);
INSERT INTO t1 VALUES (1,'2011-05-05'),(2,'2011-05-05'),(3,'2011-05-05'),(4,'2011-05-05'),(5,'2011-05-05');

SELECT * FROM t1 WHERE (f1) IN ( SELECT f1 FROM t1); returns correctly 5 rows
SELECT * FROM t1 WHERE (f1, f2) IN ( SELECT f1 , '2011-05-05' FROM t1); returns 5 rows

SELECT * FROM t1 WHERE (f1, f2) IN ( SELECT f1 , MAKEDATE( 2011 , 125 ) FROM t1); returns 1 row and 4 warnings

Related branches

Changed in maria:
milestone: none → 5.3
assignee: nobody → Sergei (sergii)
summary: - MAKEDATE in subquery context causes wrong result and bogus warnings in
- mysql-5.1-micro
+ Datetime functions in subquery context cause wrong result and bogus
+ warnings in mysql-5.1-micro
description: updated
Philip Stoev (pstoev-askmonty) wrote :

Another example:

CREATE TABLE t1 (f1 DATE);
INSERT INTO t1 VALUES (CURDATE()),(CURDATE()),(CURDATE()),(CURDATE()),(CURDATE());

CREATE TABLE t2 (f1 DATE);
INSERT INTO t2 VALUES (CURDATE()),(CURDATE()),(CURDATE()),(CURDATE()),(CURDATE());

UPDATE t1 SET f1 = CURDATE() WHERE f1 IN ( SELECT CURDATE() FROM t2 WHERE TRUE );

Sergei (sergii) on 2011-03-17
Changed in maria:
status: New → In Progress
importance: Undecided → High
Sergei (sergii) on 2011-03-17
Changed in maria:
status: In Progress → Fix Committed
Sergei (sergii) on 2011-10-28
Changed in maria:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers