Bug #28261 Datetime comparison with user var yields incorrect results
Submitted: 5 May 2007 16:47 Modified: 12 Jun 2007 17:53
Reporter: Peter Brawley (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.37, 5.1, 4.1 OS:Any
Assigned to: Evgeny Potemkin

[5 May 2007 16:47] Peter Brawley
Description:
Comparing a DATETIME column value with a user var can yield incorrect results in 5.0.37. 4.1.21 and 5.1.17 return expected results.

How to repeat:
DROP TABLE IF EXISTS animals,cats;
CREATE TABLE animals (
  ID int unsigned NOT NULL auto_increment,
  date datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=949 ;
INSERT INTO animals VALUES (10, '2007-04-30 10:52:46');
INSERT INTO animals VALUES (20, '2007-05-01 04:25:54');
INSERT INTO animals VALUES (1, '2007-05-03 06:46:10');
CREATE TABLE cats (
  ID int unsigned NOT NULL auto_increment,
  animal_ID int unsigned NOT NULL default '0',
  catname tinytext NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
INSERT INTO cats VALUES (1, 1, 'lani');
INSERT INTO cats VALUES (2, 10, 'suzi');
INSERT INTO cats VALUES (3, 20, 'Tigger');
INSERT INTO cats VALUES (4, 10, 'Rachel');
INSERT INTO cats VALUES (5, 10, 'Coconut');
INSERT INTO cats VALUES (12, 10, 'Blanker');
INSERT INTO cats VALUES (32, 10, 'Smith');

-- To list dates and ids, and suppress repeating dates, this works in 4.1 and 5.1...

SET @prev := '';
SELECT 
  IF( @prev = a.date, '', @prev := a.date ) AS animal, 
  c. animal_ID AS id
FROM animals AS a
JOIN cats AS c ON a.ID = c .animal_ID
ORDER BY a.date, c.animal_ID;
+---------------------+----+
| animal              | id |
+---------------------+----+
| 2007-04-30 10:52:46 | 10 |
|                     | 10 |
|                     | 10 |
|                     | 10 |
|                     | 10 |
| 2007-05-01 04:25:54 | 20 |
| 2007-05-03 06:46:10 |  1 |
+---------------------+----+

-- but in 5.0.37 the above query yields...

+---------------------+----+
| animal              | id |
+---------------------+----+
| 2007-04-30 10:52:46 | 10 |
| 2007-04-30 10:52:46 | 10 |
|                     | 10 |
|                     | 10 |
|                     | 10 |
| 2007-05-01 04:25:54 | 20 |
| 2007-05-03 06:46:10 |  1 |
+---------------------+----+

-- as does ...

SET @prev := TIMESTAMP('2100-12-31 00:00:00');
SELECT 
  IF( @prev = TIMESTAMP(a.date), '', (@prev := TIMESTAMP(a.date)) ) AS animal, 
  c. animal_ID AS id
FROM animals AS a
JOIN cats AS c ON a.ID = c .animal_ID
ORDER BY a.date, c.animal_ID;

-- To get the desired result from 5.0.37, we have to use timestamps, eg...

SET @prev := TIMESTAMP('2007-04-01');
SELECT
  IF( TIMESTAMPDIFF( SECOND, @prev, TIMESTAMP(a.date)) = 0, '', (@prev := TIMESTAMP(a.date)) )
  AS visitdate,
  c.animal_id AS Animal
FROM animals AS a
JOIN cats AS c ON a.ID = c.animal_ID
ORDER BY a.date ASC, a.ID;
[17 May 2007 19:12] Bugs System
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/26922

ChangeSet@1.2490, 2007-05-17 23:09:45+04:00, evgen@moonbone.local +3 -0
  Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
  is involved.
  
  The Arg_comparator::compare_datetime() comparator caches its arguments if
  they are constants i.e. const_item() returns true. The
  Item_func_get_user_var::const_item() returns true or false based on
  the current query_id and the query_id where the variable was created.
  Thus even if a query can change its value its const_item() still will return
  true. All this leads to a wrong comparison result when an object of the
  Item_func_get_user_var class is involved.
  
  Now the Arg_comparator::can_compare_as_dates() and the
  get_datetime_value() functions never cache result of the GET_USER_VAR()
  function (the Item_func_get_user_var class).
[20 May 2007 17:11] Bugs System
Pushed into 5.0.44
[20 May 2007 17:13] Bugs System
Pushed into 5.1.19-beta
[12 Jun 2007 17:53] Peter Lavin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Noted in changelogs 5.0.44 and  5.1.19-beta.