bzr push into mysql-5.1-bugteam branch (martin.hansson:3281 to 3282) Bug#47650

[Martin Hansson]

 3282 Martin Hansson	2009-12-17
      Bug#47650: using group by with rollup without indexes
      returns incorrect results with where
      
      An outer join of a const table (outer) and a normal table
      (inner) with GROUP BY on a field from the outer table would
      optimize away GROUP BY, and thus trigger the optimization to
      do away with a temporary table if grouping was performed on
      columns from the const table, hence executing the query with
      filesort without temporary table. But this should not be
      done if there is a non-indexed access to the inner table,
      since filesort does not handle joins. It expects either ref
      access, range ditto or table scan. The join condition will
      thus not be applied.
      
      Fixed by always forcing execution with temporary table in
      the case of ROLLUP with a query involving an outer join. This
      is a slightly broader class of queries than need fixing, but
      it is hard to ascertain the position of a ROLLUP field wrt
      outer join with current query representation.
     @ mysql-test/r/join_outer.result
        Bug#47650: Test result
     @ mysql-test/t/join_outer.test
        Bug#47650: Test case
     @ sql/sql_select.cc
        Bug#47650: Fix

    modified:
      mysql-test/r/join_outer.result
      mysql-test/t/join_outer.test
      sql/sql_select.cc
 3281 Ramil Kalimullin	2009-12-17 [merge]
      Auto-merge.

    modified:
      mysql-test/r/myisam.result
      mysql-test/t/myisam.test
      sql/sql_insert.cc
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2007-05-27 19:22:44 +0000
+++ b/mysql-test/r/join_outer.result	2009-12-17 09:55:18 +0000
@@ -1254,3 +1254,38 @@ SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WH
 c	e	d
 1	0	NULL
 DROP TABLE t1,t2;
+#
+# Bug#47650: using group by with rollup without indexes returns incorrect 
+# results with where
+#
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 ( a INT, b INT );
+INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
+EXPLAIN
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 LEFT JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 LEFT JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+a	COUNT( t2.b )	SUM( t2.b )	MAX( t2.b )
+1	3	6	3
+NULL	3	6	3
+EXPLAIN
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+a	COUNT( t2.b )	SUM( t2.b )	MAX( t2.b )
+1	3	6	3
+NULL	3	6	3
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2007-06-06 17:57:07 +0000
+++ b/mysql-test/t/join_outer.test	2009-12-17 09:55:18 +0000
@@ -867,3 +867,32 @@ SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WH
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug#47650: using group by with rollup without indexes returns incorrect 
+--echo # results with where
+--echo #
+CREATE TABLE t1 ( a INT );
+INSERT INTO t1 VALUES (1);
+
+CREATE TABLE t2 ( a INT, b INT );
+INSERT INTO t2 VALUES (1, 1),(1, 2),(1, 3),(2, 4),(2, 5);
+
+EXPLAIN
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 LEFT JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 LEFT JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+
+EXPLAIN
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+
+SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b )
+FROM t1 JOIN t2 USING( a )
+GROUP BY t1.a WITH ROLLUP;
+
+DROP TABLE t1, t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-12-16 09:03:24 +0000
+++ b/sql/sql_select.cc	2009-12-17 09:55:18 +0000
@@ -7124,7 +7124,19 @@ remove_const(JOIN *join,ORDER *first_ord
   for (order=first_order; order ; order=order->next)
   {
     table_map order_tables=order->item[0]->used_tables();
-    if (order->item[0]->with_sum_func)
+    if (order->item[0]->with_sum_func ||
+        /*
+          If the outer table of an outer join is const (either by itself or
+          after applying WHERE condition), grouping on a field from such a
+          table will be optimized away and filesort without temporary table
+          will be used unless we prevent that now. Filesort is not fit to
+          handle joins and the join condition is not applied. We can't detect
+          the case without an expensive test, however, so we force temporary
+          table for all queries containing more than one table, ROLLUP, and an
+          outer join.
+         */
+        (join->tables > 1 && join->rollup.state == ROLLUP::STATE_INITED &&
+        join->outer_join))
       *simple_order=0;				// Must do a temp table to sort
     else if (!(order_tables & not_const_tables))
     {


Attachment: [text/bzr-bundle] bzr/[e-mail]