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 Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0, 5.1 OS:
Assigned to: Evgeny Potemkin 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