bk commit into 5.0 tree (gkodinov:1.2566) BUG#32268

[kgeorge]

Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-11-20 16:07:24+02:00, gkodinov@stripped +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.

  mysql-test/r/group_min_max.result@stripped, 2007-11-20 16:07:23+02:00, gkodinov@stripped +46 -0
    Bug #32268: test case

  mysql-test/t/group_min_max.test@stripped, 2007-11-20 16:07:23+02:00, gkodinov@stripped +24 -0
    Bug #32268: test case

  sql/sql_select.cc@stripped, 2007-11-20 16:07:23+02:00, gkodinov@stripped +2 -1
    Bug #32268: don't group in the temp table if already done

diff -Nrup a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
--- a/mysql-test/r/group_min_max.result	2007-06-24 09:33:52 +03:00
+++ b/mysql-test/r/group_min_max.result	2007-11-20 16:07:23 +02:00
@@ -2307,3 +2307,49 @@ a
 2
 4
 DROP TABLE t1;
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
+INSERT INTO t1 SELECT a + 1, b FROM t1;
+INSERT INTO t1 SELECT a + 2, b FROM t1;
+EXPLAIN
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	12	Using temporary; Using filesort
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+a	MIN(b)	MAX(b)
+4	1	3
+3	1	3
+2	1	3
+1	1	3
+CREATE INDEX break_it ON t1 (a, b);
+EXPLAIN
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using index for group-by
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
+a	MIN(b)	MAX(b)
+1	1	3
+2	1	3
+3	1	3
+4	1	3
+EXPLAIN
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using index for group-by; Using temporary; Using filesort
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+a	MIN(b)	MAX(b)
+4	1	3
+3	1	3
+2	1	3
+1	1	3
+EXPLAIN
+SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	break_it	10	NULL	12	Using index
+SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
+a	MIN(b)	MAX(b)	AVG(b)
+4	1	3	2.0000
+3	1	3	2.0000
+2	1	3	2.0000
+1	1	3	2.0000
+DROP TABLE t1;
diff -Nrup a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
--- a/mysql-test/t/group_min_max.test	2007-06-24 09:33:52 +03:00
+++ b/mysql-test/t/group_min_max.test	2007-11-20 16:07:23 +02:00
@@ -888,7 +888,31 @@ SELECT SQL_BIG_RESULT DISTINCT(a) FROM t
 
 DROP TABLE t1;
 
+#
+# Bug #32268: Indexed queries give bogus MIN and MAX results
+#
 
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3);
+INSERT INTO t1 SELECT a + 1, b FROM t1;
+INSERT INTO t1 SELECT a + 2, b FROM t1;
 
+EXPLAIN
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 
+CREATE INDEX break_it ON t1 (a, b);
 
+EXPLAIN
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a;
+
+EXPLAIN
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
+
+EXPLAIN
+SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
+SELECT a, MIN(b), MAX(b), AVG(b) FROM t1 GROUP BY a ORDER BY a DESC;
+
+DROP TABLE t1;
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-11-16 13:24:02 +02:00
+++ b/sql/sql_select.cc	2007-11-20 16:07:23 +02:00
@@ -10256,7 +10256,8 @@ Next_select_func setup_end_select_func(J
   /* Set up select_end */
   if (table)
   {
-    if (table->group && tmp_tbl->sum_func_count)
+    if (table->group && tmp_tbl->sum_func_count && 
+        !tmp_tbl->precomputed_group_by)
     {
       if (table->s->keys)
       {