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
• Status: Not a Bug
• 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.