| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.37, 5.1, 4.1 | OS: | Any |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[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.
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;