| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.16 | OS: | Windows (windows and linux) |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[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.
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);