Bug #22342 | No results returned for query using max and group by | ||
---|---|---|---|
Submitted: | 14 Sep 2006 12:37 | Modified: | 26 Oct 2006 3:14 |
Reporter: | David Crimmins | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.22-pro-nt-log | OS: | |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[14 Sep 2006 12:37]
David Crimmins
[14 Sep 2006 12:40]
David Crimmins
Description should read: Queries using max column function and group by return no results when the where clause contains < and references the group by column.
[14 Sep 2006 15:06]
Hartmut Holzgraefe
mysqltest test case
Attachment: bug22342.tar.gz (application/x-gzip, text), 795 bytes.
[26 Sep 2006 7:37]
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/12510 ChangeSet@1.2278, 2006-09-26 10:36:49+03:00, gkodinov@macbook.gmz +4 -0 Bug #22342: No results returned for query using max and group by When jumping to the first record after prefix the optimizer can add the range conditions over the arguments of the MIN/MAX functions to the range conditions to the GROUP BY fields. In doing so it was not opening the appropriate end of the range and was causing impossible ranges during the prefix scan, e.g. : (<const>) <= (<group by field>,<max_arg>) < (<const>,<max_const>) was trimmed to the impossible (<const>) <= (<group by field>) < (<const>) while searching for the first/next prefix to group on. This fix opens the intervals, so the above range actually transforms to (<const>) <= (<group by field>) <= (<const>) instead
[29 Sep 2006 7: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/12812 ChangeSet@1.2278, 2006-09-29 10:01:17+03:00, gkodinov@macbook.gmz +4 -0 Bug #22342: No results returned for query using max and group by When using index for group by and range access the server isolates a set of ranges based on the conditions over the key parts of the index used. Then it uses only the prefix of these ranges to jump over the possible GROUP BY column value combinations. Each range contains a notion on whether it includes its border values. When ignoring the suffix of the range conditions (to jump over the GROUP BY prefix only) the server must change the remaining intervals so they always contain their borders, e.g. if the whole range was : (1,-inf) <= (<group_by_col>,<min_max_arg_col>) < (1, 3) we must make (1) <= (<group_by_col>) <= (1) because (a,b) < (c1,c2) means : a < c1 OR (a = c1 AND b < c2).
[16 Oct 2006 16: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/13753 ChangeSet@1.2278, 2006-10-16 19:30:19+03:00, gkodinov@macbook.gmz +4 -0 Bug #22342: No results returned for query using max and group by When using index for group by and range access the server isolates a set of ranges based on the conditions over the key parts of the index used. Then it uses only the ranges over the GROUP BY fields to jump from one group to another. Since the GROUP BY fields may form a prefix over the index, we may use only a prefix of the ranges produced by the range optimizer. Each range contains a notion on whether it includes its border values. The problem is that when using a range prefix, the last range is open because it assumes that there is a range on the next keypart. Thus when we use a prefix range as it is, it excludes all border values. The solution is when ignoring the suffix of the range conditions (to jump over the GROUP BY prefix only) the server must change the remaining intervals so they always contain their borders, e.g. if the whole range was : (1,-inf) <= (<group_by_col>,<min_max_arg_col>) < (1, 3) we must make (1) <= (<group_by_col>) <= (1) because (a,b) < (c1,c2) means : a < c1 OR (a = c1 AND b < c2).
[21 Oct 2006 9:13]
Georgi Kodinov
Pushed in 5.0.27/5.1.13-beta
[26 Oct 2006 3:14]
Paul DuBois
Noted in 5.0.30, 5.1.13 changelogs.