Bug #31936 Implicit DATETIME conversions work different on 5.0.24a vs. 5.0.45
Submitted: 30 Oct 2007 9:22 Modified: 15 Nov 2007 10:52
Reporter: Tobias Asplund Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0, 5.1 BK OS:Linux
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: regression

[30 Oct 2007 9:22] Tobias Asplund
Description:
Difference in results for 5.0.24a vs. 5.0.45

running the testcase file below:

flupps@flupps:~$ mysql test < /tmp/testcase.sql 
VERSION()
5.0.45-log
a       b
6       2007-10-24
flupps@flupps:~$ mysql -S /tmp/5204-mysql.sock test < /tmp/testcase.sql 
VERSION()
5.0.24a
a       b
6       2007-10-24
a       b
6       2007-10-24
a       b
6       2007-10-24
a       b
3       2007-10-27

How to repeat:
place this in a file:

CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  a INT,
  b DATE,
  INDEX (b)
);
SELECT VERSION();
SET @a = 0;
INSERT INTO t1 VALUES (@a:=@a+1, NOW() - INTERVAL @a DAY);
INSERT INTO t1 VALUES (@a:=@a+1, NOW() - INTERVAL @a DAY);
INSERT INTO t1 VALUES (@a:=@a+1, NOW() - INTERVAL @a DAY);
INSERT INTO t1 VALUES (@a:=@a+1, NOW() - INTERVAL @a DAY);
INSERT INTO t1 VALUES (@a:=@a+1, NOW() - INTERVAL @a DAY);
INSERT INTO t1 VALUES (@a:=@a+1, NOW() - INTERVAL @a DAY);
SELECT CONCAT(MIN(b), ' 11:11:11') FROM t1 INTO @b;
SELECT * FROM t1 WHERE b = @b;
SELECT * FROM t1 WHERE b = CAST(@b AS DATETIME);
SELECT * FROM t1 WHERE b = CAST(@b AS DATE);
SELECT * FROM t1 WHERE b = '2007-10-27 10:10:10';

Run this file against 5.0.24a and 5.0.45 and see big differences in results.
(You may need to update the last row to get testcase to work properly
[30 Oct 2007 10:44] Sveta Smirnova
Thank you for the report.

Verified as described. Seems to be introduced by fix of bug #16546
[15 Nov 2007 10:52] Alexey Botchkov
Expected behaviour.
only this query should return nonempty result:
SELECT * FROM t1 WHERE b = CAST(@b AS DATE);

as it works.