[MDEV-3091] LP:736370 - Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micro Created: 2011-03-16  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug736370.xml    

 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

 Comments   
Comment by Philip Stoev (Inactive) [ 2011-03-16 ]

Re: Datetime functions in subquery context cause wrong result and bogus warnings in mysql-5.1-micro
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 );

Comment by Rasmus Johansson (Inactive) [ 2011-10-28 ]

Launchpad bug id: 736370

Generated at Thu Feb 08 06:46:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.