Bug #27659 | SELECT DISTINCT returns incorrect result set when field is repeated | ||
---|---|---|---|
Submitted: | 4 Apr 2007 22:40 | Modified: | 17 Apr 2007 20:16 |
Reporter: | Paul Keenan | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | Ver 14.12 Distrib 5.0.37, for Win32 (ia3, 5.1, 5.2 | OS: | Linux (, Win XP Professional v5.1.2600 Service Pack 2 Build 2600) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[4 Apr 2007 22:40]
Paul Keenan
[5 Apr 2007 8:24]
Sveta Smirnova
Thank you for the report. Verified as described. Workaround: use GROUP BY
[6 Apr 2007 11:18]
Timour Katchaounov
Changed priority to P2 as this is wrong result.
[6 Apr 2007 14:30]
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/23987 ChangeSet@1.2435, 2007-04-06 17:30:10+03:00, gkodinov@magare.gmz +3 -0 Bug #27659: The optimizer transforms DISTINCT into a GROUP BY when possible. It does that by constructing the same structure (a list of ORDER instances) the parser makes when parsing GROUP BY. While doing that it also eliminates duplicates. But if a duplicate is found it doesn't advance the pointer to ref_pointer array, so the next (and subsequent) ORDER structures point to the wrong element in the SELECT list. Fixed by advancing the pointer in ref_pointer_array even in the case of a duplicate.
[10 Apr 2007 13:56]
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/24173 ChangeSet@1.2435, 2007-04-10 16:55:48+03:00, gkodinov@magare.gmz +3 -0 Bug #27659: The optimizer transforms DISTINCT into a GROUP BY when possible. It does that by constructing the same structure (a list of ORDER instances) the parser makes when parsing GROUP BY. While doing that it also eliminates duplicates. But if a duplicate is found it doesn't advance the pointer to ref_pointer array, so the next (and subsequent) ORDER structures point to the wrong element in the SELECT list. Fixed by advancing the pointer in ref_pointer_array even in the case of a duplicate.
[15 Apr 2007 16:48]
Bugs System
Pushed into 5.1.18-beta
[15 Apr 2007 16:53]
Bugs System
Pushed into 5.0.40
[17 Apr 2007 20:16]
Paul DuBois
Noted in 5.0.40, 5.1.18 changelogs. SELECT DISTINCT could return incorrect results if the select list contained duplicated columns.