MySQL Bugs: #28778: Wrong results from datetime column comparing short, numeric date with BETWEEN

Bug #28778	Wrong results from datetime column comparing short, numeric date with BETWEEN
Submitted:	30 May 2007 15:37	Modified:	15 Jun 2007 9:21
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.42	OS:	Any
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any
Tags:	between, date, datetime, regression, wrong result

Description:
A change has been introduced in 5.0.42 that breaks some datetime comparisons when using BETWEEN and a "short", numeric form of a date (i.e., 20070501 and not '2007-05-01').

From the test below, you'll see that the first query returns 2 rows of data in 5.0.42.  It only returns 1 row in versions previous to 5.0.42.

Note that while I was testing, I created an index on the `date_time` column.  When I created this index, the query worked fine in 5.0.42, so there is some sort of bug (whether the initial was an intended change or not).

How to repeat:
CREATE TABLE `test` ( 
`id` int(10) unsigned NOT NULL auto_increment, 
`date_time` datetime NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=MyISAM;

INSERT INTO test VALUES (null, '2007-05-02 10:12:15'), (null, '2007-04-02 10:12:15');

SELECT * FROM test WHERE date_time between 20070501 AND 20070529235959; --> returns BOTH rows

SELECT * FROM test WHERE date_time between '2007-05-01' AND '2007-05-29 23:59:59'; --> returns ONE row

Note that if you create an index on the `date_time` column:

CREATE INDEX idx ON `test` (`date_time`);

Then, the query will work as expected (i.e., only return 1 row in 5.0.42).

Suggested fix:
As a work-around, creating an index may work for you, as it did in this case.  And is probably a good idea to have an index on this column anyways if you'll be using it to select against.

Also, you can simply reqrite the query, as the second example above.

As for the fix, I am not sure.  But it does look like it was introduced from the fix for bug #27590.

http://bugs.mysql.com/bug.php?id=27590

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28152

ChangeSet@1.2514, 2007-06-06 00:02:16+04:00, evgen@moonbone.local +3 -0
  Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
  integer constants.
  
  This bug is introduced by the fix for bug#16377. Before it the 
  Item_func_between::fix_length_and_dec method converted second and third
  arguments to the type of the first argument is they were const and the first
  argument is of the DATE/DATETIME type. That approach worked well for integer
  constants and sometimes produced bad result for string constants. The fix for
  the bug#16377 wrongly removed that code at all. Due to that fact the comparison
  of a datetime field and an integer constant was carried out in a wrong way
  and sometimes with a wrong result.
  
  Now the Item_func_between::fix_length_and_dec method converts second and third
  arguments to the type of the first argument is they are constant, the first
  argument is of the DATE/DATETIME type and the DATETIME comparator isn't
  applicable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28154

ChangeSet@1.2514, 2007-06-05 23:41:06+04:00, evgen@moonbone.local +3 -0
  Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
  integer constants.
  
  This bug is introduced by the fix for bug#16377. Before it the 
  Item_func_between::fix_length_and_dec method converted second and third
  arguments to the type of the first argument is they were const and the first
  argument is of the DATE/DATETIME type. That approach worked well for integer
  constants and sometimes produced bad result for string constants. The fix for
  the bug#16377 wrongly removed that code at all. Due to that fact the comparison
  of a datetime field and an integer constant was carried out in a wrong way
  and sometimes with a wrong result.
  
  Now the Item_func_between::fix_length_and_dec method converts second and third
  arguments to the type of the first argument is they are constant, the first
  argument is of the DATE/DATETIME type and the DATETIME comparator isn't
  applicable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28156

ChangeSet@1.2514, 2007-06-06 00:25:06+04:00, evgen@moonbone.local +3 -0
  Bug#28778: Wrong result of BETWEEN when comparing a DATETIME field with an
  integer constants.
  
  This bug is introduced by the fix for bug#16377. Before the fix the 
  Item_func_between::fix_length_and_dec method converted the second and third
  arguments to the type of the first argument if they were constant and the first
  argument is of the DATE/DATETIME type. That approach worked well for integer
  constants and sometimes produced bad result for string constants. The fix for
  the bug#16377 wrongly removed that code at all and as a result of this the
  comparison of a DATETIME field and an integer constant was carried out in a
  wrong way and sometimes led to wrong result sets.
  
  Now the Item_func_between::fix_length_and_dec method converts the second and
  third arguments to the type of the first argument if they are constant, the
  first argument is of the DATE/DATETIME type and the DATETIME comparator isn't
  applicable.

Pushed into 5.1.20-beta

Pushed into 5.0.44

A note has been added to the 5.1.20 and 5.0.44 changelogs.