MySQL Bugs: #32268: Indexed queries give bogus MIN and MAX results

Bug #32268	Indexed queries give bogus MIN and MAX results
Submitted:	12 Nov 2007 8:13	Modified:	23 Jan 2008 16:25
Reporter:	William Grant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45, 5.1.22	OS:	Linux (Ubuntu 7.10)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
If I create a query like so, with an index on (key, value):

 SELECT `key`, MIN(`value`), MAX(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;

... I get bogus results. The returned MIN and MAX results are identical. If I add an AVG there too, everything is good. If I drop the index, everything is also good. If I change the sorting (for example, removing the DESC), all is also good.

I've seen this on both 5.0.38 and 5.0.45.

How to repeat:
DROP TABLE breakage;
CREATE TABLE `breakage` (
  `id` int NOT NULL auto_increment,
  `key` int NOT NULL,
  `value` float NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `breakage` (`key`, `value`) VALUES
(1,1),
(1,2),
(1,3),
(2,4),
(2,5),
(2,6),
(3,7),
(3,8),
(3,9);

SELECT `key`, MIN(`value`), MAX(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;

CREATE INDEX `break_it` ON `breakage` (`key`, `value`);

SELECT `key`, MIN(`value`), MAX(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;
SELECT `key`, MIN(`value`), MAX(`value`), AVG(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;

Thank you for this detailed bug report.  I have reproduced the behavior as described.

mysql> SELECT `key`, MIN(`value`), MAX(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;
+-----+--------------+--------------+
| key | MIN(`value`) | MAX(`value`) |
+-----+--------------+--------------+
|   3 |            9 |            9 | 
|   2 |            6 |            6 | 
|   1 |            3 |            3 | 
+-----+--------------+--------------+
3 rows in set (0.01 sec)

mysql> SELECT `key`, MIN(`value`), MAX(`value`), AVG(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;
+-----+--------------+--------------+--------------+
| key | MIN(`value`) | MAX(`value`) | AVG(`value`) |
+-----+--------------+--------------+--------------+
|   3 |            7 |            9 |            8 | 
|   2 |            4 |            6 |            5 | 
|   1 |            1 |            3 |            2 | 
+-----+--------------+--------------+--------------+
3 rows in set (0.00 sec)

mysql> alter table breakage drop index `break_it`; 
Query OK, 9 rows affected, 0 warnings (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT `key`, MIN(`value`), MAX(`value`) FROM breakage GROUP BY `key` ORDER BY `key` DESC;
+-----+--------------+--------------+
| key | MIN(`value`) | MAX(`value`) |
+-----+--------------+--------------+
|   3 |            7 |            9 | 
|   2 |            4 |            6 | 
|   1 |            1 |            3 | 
+-----+--------------+--------------+
3 rows in set (0.00 sec)

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/37956

ChangeSet@1.2566, 2007-11-16 17:31:50+02:00, gkodinov@magare.gmz +3 -0
  Bug #32268: Indexed queries give bogus MIN and MAX results
  
  Loose index scan does not work correctly on indexes in 
  descending order.
  Fixed the bug by disabling loose index scan when sorting 
  in descending order.ug #32268: Indexed queries give bogus MIN and MAX results
  
  Loose index scan does not work correctly on indexes in 
  descending order.
  Fixed the bug by disabling loose index scan when sorting 
  in descending order.

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/38136

ChangeSet@1.2566, 2007-11-20 16:07:24+02:00, gkodinov@magare.gmz +3 -0
  Bug #32268: Indexed queries give bogus MIN and MAX results
  
  Loose index scan does the grouping so the temp table does 
  not need to do it, even when sorting.
  Fixed by checking if the grouping is already done before
  doing sorting and grouping in a temp table and do only 
  sorting.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

MIN() and MAX() could return incorrect results when an index was
present if a loose index scan was used.