MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Martin Hansson Date:December 11 2009 2:04pm
Subject:bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)
Bug#47650
View as plain text  
#At file:///Users/martinhansson/bzr/bug47650/5.1bt-commit/ based on revid:alexey.kopytov@stripped

 3200 Martin Hansson	2009-12-11
      Bug#47650: using group by with rollup without indexes
      returns incorrect results with where
      
      An outer join of a const table and a normal table with GROUP
      BY 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, executing
      the query with filesort without temporary table. But this
      should not be done if there is a non-indexed access to the
      joined table, since filesort expects either ref access, range
      ditto or table scan. Any WHERE or join condition is lost
      after optimization and it is re-created only for the
      aforementioned key-based access methods.
      
      Fixed by always forcing execution with temporary table in
      the case of ROLLUP with a query involving 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
=== 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-11 14:04:47 +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;
+#
+# using group by with rollup without indexes returns incorrect results 
+# with where
+#
+CREATE TABLE t1 ( a INT PRIMARY KEY );
+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	PRIMARY	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-11 14:04:47 +0000
@@ -867,3 +867,32 @@ SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WH
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # using group by with rollup without indexes returns incorrect results 
+--echo # with where
+--echo #
+CREATE TABLE t1 ( a INT PRIMARY KEY );
+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-11-12 17:14:07 +0000
+++ b/sql/sql_select.cc	2009-12-11 14:04:47 +0000
@@ -7122,7 +7122,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 inner table of an outer join is const (either by itself or
+          after applying WHERE condition), GROUP BY will be optimized away
+          unless we prevent that now. It is not safe to run filesort without
+          temporary table in this case. The WHERE condition (may originally be
+          the join condition) is not applied by filesort. We can't detect this
+          case without an expensive test, however, so we simply check that
+          there is more than one table, ROLLUP, and an outer join in the
+          query.
+         */
+        (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/martin.hansson@sun.com-20091211140447-zav1cgvoi5zsko99.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Martin Hansson11 Dec
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Evgeny Potemkin12 Dec
    • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Martin Hansson14 Dec