Bug #16255 Subquery in where
Submitted: 6 Jan 2006 16:07 Modified: 7 Sep 2006 0:34
Reporter: Carl Bosch Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1.16 OS:Microsoft Windows (windows and linux)
Assigned to: Georgi Kodinov CPU Architecture:Any

[6 Jan 2006 16:07] Carl Bosch
Description:
I have a table with id, status and date

I tried to get the last status row in a subquery

select * from retailerStatus r1 WHERE (r1.retailerID,(r1.changed)) in
(SELECT r2.retailerId,(max(changed)) from retailerStatus r2 group by r2.retailerId);

but I only got 1 row as a result, 

the strange think of this is that this stmt:
select * from retailerStatus r1 WHERE (r1.retailerID,(r1.changed)) in
(SELECT r2.retailerId+'',(max(changed)+'') from retailerStatus r2 group by r2.retailerId);

returns 4 rows as expected, this seems so strange to :-S
I tested the same case on mysql 4.1.5.gamma and it worked without the +''

Hope it helped to avoid a lot of anger,

greetz

Carl

How to repeat:
CREATE TABLE `retailerStatus` (
  `retailerID` varchar(8) collate latin1_german1_ci NOT NULL default '',
  `statusID` int(10) unsigned NOT NULL default '0',
  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
  UNIQUE KEY `retailerID` (`retailerID`,`statusID`,`changed`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

INSERT INTO retailerStatus VALUES("0026", "1", "2005-12-06 12:18:56");
INSERT INTO retailerStatus VALUES("0026", "2", "2006-01-06 12:25:53");
INSERT INTO retailerStatus VALUES("0037", "1", "2005-12-06 12:18:56");
INSERT INTO retailerStatus VALUES("0037", "2", "2006-01-06 12:25:53");
INSERT INTO retailerStatus VALUES("0048", "1", "2006-01-06 12:37:50");
INSERT INTO retailerStatus VALUES("0059", "1", "2006-01-06 12:37:50");

select * from retailerStatus r1 WHERE (r1.retailerID,(r1.changed)) in
(SELECT r2.retailerId,(max(changed)) from retailerStatus r2 group by r2.retailerId);

Suggested fix:
select * from retailerStatus r1 WHERE (r1.retailerID,(r1.changed)) in
(SELECT r2.retailerId+'',(max(changed)+'') from retailerStatus r2 group by r2.retailerId);
[8 Jan 2006 17:12] Hartmut Holzgraefe
works in 4.1.12, broken in 4.1.14 and above (i don't have a working 4.1.13 installation around right now so i can't test that one...)
[8 Jan 2006 17:15] Hartmut Holzgraefe
broken in current 5.0, too
[8 Aug 2006 15:15] 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/10160

ChangeSet@1.2532, 2006-08-08 18:14:46+03:00, gkodinov@macbook.gmz +3 -0
  Bug #16255: Subquery in where
   Must not use Item_direct_ref in HAVING because it points to
   the new value (witch is not yet calculated for the first row).
[24 Aug 2006 14:01] 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/10832

ChangeSet@1.2542, 2006-08-24 17:49:53+04:00, sergefp@mysql.com +3 -0
  Bug #16255: Subquery in WHERE (the cset by Georgi Kodinov)
   Must not use Item_direct_ref in HAVING because it points to
   the new value (witch is not yet calculated for the first row).
[24 Aug 2006 14:33] 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/10837

ChangeSet@1.2543, 2006-08-24 18:33:23+04:00, sergefp@mysql.com +2 -0
  BUG#16255: Post-review fixes: adjust the testcase.
[24 Aug 2006 16:55] 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/10848

ChangeSet@1.2269, 2006-08-24 20:56:28+04:00, sergefp@mysql.com +3 -0
  BUG#16255: Merge to 5.0
  MERGE: 1.1616.2696.4
[29 Aug 2006 13:20] Evgeny Potemkin
Fixed in 4.1.22, 5.0.25
[4 Sep 2006 11:41] Evgeny Potemkin
Fixed in 5.1.12
[7 Sep 2006 0:34] Jon Stephens
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

Documented bugfix in 4.1.22/5.0.25/5.1.12 changelogs.