Bug #17841 HAVING ... BETWEEN does not treat a Quoted DATETIME without delimiters properly

• Submitted: 1 Mar 2006 20:51
• Modified: 3 May 2006 11:49
• Reporter: Chris Calender
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.0, 4.1, 5.0, 5.1
• Assigned to: Assigned Account
• CPU Architecture: Any

[1 Mar 2006 20:51] Chris Calender

Description:

If you quote a DATE without using delimiters (i.e., '20060301000000') and use it in the HAVING ... BETWEEN clause, it does not treat it as a datetime.  If it is unquoted, then it works fine.

This also affects NOW() as well.

How to repeat:
CREATE TABLE t1 ( a DATETIME );

INSERT INTO t1 VALUES ('2006-03-01 01:02:03');

SELECT a FROM t1 WHERE a BETWEEN '20060301000000' AND '20060301235959';
- returns the row

SELECT a FROM t1 WHERE a GROUP BY a HAVING a BETWEEN '20060301000000' AND '20060301235959';
- returns the empty set

SELECT a FROM t1 WHERE a GROUP BY a HAVING a BETWEEN 20060301000000 AND 20060301235959;
- returns the row

------------------------------

A second example shows that this affects NOW() as well:

SET @a:= CURDATE();
SET @a:= REPLACE(CURDATE(),'-','');
SELECT @a;
- returns 20060301

SELECT NOW() BETWEEN CONCAT(@a,'000000') AND CONCAT(@a,'235959');
- returns 0 - false

To show that NOW() is also affected

SELECT NOW() BETWEEN CONCAT(@a,'000000')+0 AND CONCAT(@a,'235959')+0;
- returns 1 - true

[3 May 2006 11:49] Evgeny Potemkin

Duplicate of the bug#16377