bzr commit into mysql-5.1-bugteam branch (martin.hansson:3191) Bug#47650

[Martin Hansson]

#At file:///data0/martin/bzr/bug47650/5.1bt/ based on revid:kristofer.pettersson@stripped

 3191 Martin Hansson	2009-11-10
      Bug#47650: using group by with rollup without indexes
                 returns incorrect results with where
      
      A result set for a ROLLUP query outer-joining tables t1 and
      t2 turned t1 into a const table after applying the join
      condition, which in turn caused GROUP BY to be optimized
      away. A filtered table scan was then used on t2 in the
      join. The const access and lack of GROUP BY caused the
      optimizer to use filesort without temporary table to access
      t2. But the join condition, translated into a WHERE is lost
      in the optimization step. Filesort does no handle filtered
      table scans.
      
      Fixed by always sorting with a temporary table if a query
      contains ROLLUP and there is no key to access the joined
      table.
     @ mysql-test/r/func_gconcat.result
        Bug#47650: Expected change in test result
     @ mysql-test/r/group_by.result
        Bug#47650: Test result
     @ mysql-test/r/olap.result
        Bug#47650: Expected change in test result
     @ mysql-test/t/group_by.test
        Bug#47650: Test case
     @ sql/sql_select.cc
        Bug#47650: Fix and organizing surrounding code according to
        coding standard

    modified:
      mysql-test/r/func_gconcat.result
      mysql-test/r/group_by.result
      mysql-test/r/olap.result
      mysql-test/t/group_by.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/func_gconcat.result'
--- a/mysql-test/r/func_gconcat.result	2008-05-01 11:54:59 +0000
+++ b/mysql-test/r/func_gconcat.result	2009-11-10 14:02:26 +0000
@@ -505,7 +505,7 @@ d	a	b
 8	2	3
 explain select a, group_concat(b) from t1 group by a with rollup;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using temporary; Using filesort
 select a, group_concat(b) from t1 group by a with rollup;
 a	group_concat(b)
 1	3,4,2,1,2

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2009-02-26 17:17:06 +0000
+++ b/mysql-test/r/group_by.result	2009-11-10 14:02:26 +0000
@@ -1703,3 +1703,26 @@ COUNT(i)
 1
 DROP TABLE t1;
 SET @@sql_mode = @old_sql_mode;
+#
+# 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
+DROP TABLE t1, t2;
+End of 5.1 tests

=== modified file 'mysql-test/r/olap.result'
--- a/mysql-test/r/olap.result	2009-11-06 14:54:19 +0000
+++ b/mysql-test/r/olap.result	2009-11-10 14:02:26 +0000
@@ -88,7 +88,7 @@ TV	NULL	NULL	600
 NULL	NULL	NULL	7785
 explain extended select product, country_id , year, sum(profit) from t1 group by product, country_id, year with rollup;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	100.00	Using temporary; Using filesort
 Warnings:
 Note	1003	select `test`.`t1`.`product` AS `product`,`test`.`t1`.`country_id` AS `country_id`,`test`.`t1`.`year` AS `year`,sum(`test`.`t1`.`profit`) AS `sum(profit)` from `test`.`t1` group by `test`.`t1`.`product`,`test`.`t1`.`country_id`,`test`.`t1`.`year` with rollup
 select product, country_id , sum(profit) from t1 group by product desc, country_id with rollup;
@@ -687,7 +687,7 @@ C
 NULL
 EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using temporary; Using filesort
 DROP VIEW v1;
 DROP TABLE t1;
 CREATE TABLE t1 (a int(11) NOT NULL);

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2009-02-26 17:17:06 +0000
+++ b/mysql-test/t/group_by.test	2009-11-10 14:02:26 +0000
@@ -1158,3 +1158,25 @@ SELECT COUNT(i) FROM t1 WHERE i > 1;
 DROP TABLE t1;
 SET @@sql_mode = @old_sql_mode;
 
+--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;
+
+DROP TABLE t1, t2;
+
+--echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-11-06 14:54:19 +0000
+++ b/sql/sql_select.cc	2009-11-10 14:02:26 +0000
@@ -1414,9 +1414,12 @@ JOIN::optimize()
                                     &join_tab[const_tables].table->
                                     keys_in_use_for_group_by))) ||
          select_distinct) &&
-        tmp_table_param.quick_group && !procedure)
+        (tmp_table_param.quick_group || rollup.state == ROLLUP::STATE_INITED) &&
+        !procedure)
     {
-      need_tmp=1; simple_order=simple_group=0;	// Force tmp table without sort
+      /* Force tmp table without sort */
+      need_tmp= 1;
+      simple_order= simple_group= 0;
     }
     if (order)
     {


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