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-16 17:31:50+02:00, gkodinov@stripped +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.

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

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

  sql/opt_range.cc@stripped, 2007-11-16 17:31:49+02:00, gkodinov@stripped +10 -0
    Bug #32268: Disable loose index scan when sorting in descending order

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-16 17:31:49 +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	index	NULL	break_it	10	NULL	12	Using index
+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-16 17:31:49 +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/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc	2007-10-23 14:32:03 +03:00
+++ b/sql/opt_range.cc	2007-11-16 17:31:49 +02:00
@@ -7519,6 +7519,8 @@ cost_group_min_max(TABLE* table, KEY *in
            of GA. Since P is applied to only GROUP attributes it filters some
            groups, and thus can be applied after the grouping.
     GA4. There are no expressions among G_i, just direct column references.
+    OBY1.If Q has a ORDER BY clause, then no ORDER BY item is in descending
+         order.
     NGA1.If in the index I there is a gap between the last GROUP attribute G_k,
          and the MIN/MAX attribute C, then NGA must consist of exactly the index
          attributes that constitute the gap. As a result there is a permutation
@@ -7576,6 +7578,7 @@ cost_group_min_max(TABLE* table, KEY *in
     before this point. Is this correct, or do we have to check it completely?
   - Lift the limitation in condition (B3), that is, make this access method 
     applicable to ROLLUP queries.
+  - Lift OBY1.
 
   RETURN
     If mem_root != NULL
@@ -7668,6 +7671,13 @@ get_best_group_min_max(PARAM *param, SEL
   for (tmp_group= join->group_list; tmp_group; tmp_group= tmp_group->next)
   {
     if ((*tmp_group->item)->type() != Item::FIELD_ITEM)
+      DBUG_RETURN(NULL);
+  }
+
+  /* Check (OBY1) - that there is no descending order in ORDER BY. */
+  for (tmp_group= join->order; tmp_group; tmp_group= tmp_group->next)
+  {
+    if (!tmp_group->asc)
       DBUG_RETURN(NULL);
   }