Bug #3680 BETWEEN with TIME column date arithmetic failing

• Submitted: 6 May 2004 20:21
• Modified: 3 Sep 2004 13:36
• Reporter: Dean Ellis
• Status: Won't fix
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 4.0.19
• OS: Any
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[6 May 2004 20:21] Dean Ellis

Description:
A query of this form (with t1.a INT and t2.a TIME) returns results:

SELECT * FROM t1, t2 WHERE t2.a BETWEEN '2004-05-06 03:00:00' - INTERVAL 120 MINUTE AND '03:00:00';

Replacing the constant with a column reference fails:

SELECT * FROM t1, t2 WHERE t2.a BETWEEN '2004-05-06 03:00:00' - INTERVAL t1.a MINUTE AND '03:00:00';

How to repeat:
Test to be attached.

Suggested fix:
n/a

[27 Sep 2008 10:05] Konstantin Osipov

Still repeatable in 5.1.

Test case:
CREATE TABLE t1 ( a int );
INSERT INTO t1 VALUES (120);
CREATE TABLE t2 ( a time );
INSERT INTO t2 VALUES ('03:00:00'),('00:01:00');
SELECT * FROM t1, t2 WHERE t2.a BETWEEN '2004-05-06 03:00:00' - INTERVAL 120 MINUTE AND '03:00:00';
SELECT * FROM t1, t2 WHERE t2.a BETWEEN '2004-05-06 03:00:00' - INTERVAL t1.a MINUTE AND '03:00:00';
DROP TABLE t1, t2;

[30 Sep 2008 9:12] Konstantin Osipov

See also bugs:
Bug#5054
Bug#21677