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:
None 
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
Description:
Queries using max column function and group by return no results when the where clause contains  <= and references the group by column.

eg:

select max(time), id from group_by_bug 
where time < 3 and id in (1,2)
group by id;
 

How to repeat:
mysql> drop table if exists group_by_bug;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE TABLE group_by_bug (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `time` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY  (`id`,`time`),
    ->   KEY `dsFirstTime` (`time`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql>
mysql> insert into `group_by_bug` values (1,1), (2,1), (3,1),
    ->                                   (1,2), (2,2), (3,2),
    ->                                   (1,3), (2,3), (3,3);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> -- following should return two row but does not
mysql> select max(time), id from group_by_bug
    -> where time < 3 and id in (1,2)
    -> group by id;
Empty set (0.00 sec)

mysql>
mysql>
mysql> -- however following does return expected results
mysql> select max(time), id from group_by_bug
    -> where time < 3
    -> group by id;
+-----------+----+
| max(time) | id |
+-----------+----+
|         2 |  1 |
|         2 |  2 |
|         2 |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- and this works as well
mysql> select max(time), id from group_by_bug
    -> where time <= 2 and id in (1,2)
    -> group by id;
+-----------+----+
| max(time) | id |
+-----------+----+
|         2 |  1 |
|         2 |  2 |
+-----------+----+
2 rows in set (0.00 sec)
[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.