bzr commit into mysql-trunk branch (jorgen.loland:3520) Bug#47853

[Jorgen Loland]

#At file:///export/home/jl208045/mysql/mysql-trunk-47853/ based on revid:hezx@stripped

 3520 Jorgen Loland	2011-01-17
      BUG#47853: Implicitly grouped queries do not need to use 
                 temporary tables
      
      A query is implicitly grouped if it contains an aggregate 
      function but no GROUP BY clause. Implicitly grouped queries 
      return zero or one row, so ordering does not make sense. 
      
      This patch removes the order by clause during the prepare()
      stage of the optimizer when implicitly ordered queries are 
      detected.
     @ mysql-test/r/func_group.result
        Updated result file
     @ mysql-test/r/limit.result
        Updated result file
     @ mysql-test/r/subquery_nomat_nosj.result
        Updated result file
     @ mysql-test/r/subquery_none.result
        Updated result file
     @ sql/sql_select.cc
        Remove order by clause during prepare() if the query is implicitly grouped.

    modified:
      mysql-test/r/func_group.result
      mysql-test/r/limit.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_none.result
      sql/sql_select.cc
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result	2010-12-21 12:34:13 +0000
+++ b/mysql-test/r/func_group.result	2011-01-17 12:19:52 +0000
@@ -1507,7 +1507,7 @@ SELECT MAX(pk) as max, i
 FROM t1
 ORDER BY max;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
 
 # Only 11 is correct for collumn i in this result
 SELECT MAX(pk) as max, i

=== modified file 'mysql-test/r/limit.result'
--- a/mysql-test/r/limit.result	2008-10-15 21:34:51 +0000
+++ b/mysql-test/r/limit.result	2011-01-17 12:19:52 +0000
@@ -80,13 +80,13 @@ create table t1 (a int);
 insert into t1 values (1),(2),(3),(4),(5),(6),(7);
 explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 c
 7
 explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 c
 28

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-01-12 12:21:16 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-01-17 12:19:52 +0000
@@ -5088,7 +5088,7 @@ SELECT 1 FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
 ORDER BY count(*);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using index; Using temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using index
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 # should not crash the next statement

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-01-12 12:21:16 +0000
+++ b/mysql-test/r/subquery_none.result	2011-01-17 12:19:52 +0000
@@ -5087,7 +5087,7 @@ SELECT 1 FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
 ORDER BY count(*);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using index; Using temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using index
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 # should not crash the next statement

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-14 08:20:08 +0000
+++ b/sql/sql_select.cc	2011-01-17 12:19:52 +0000
@@ -749,7 +749,11 @@ JOIN::prepare(Item ***rref_pointer_array
   unit= unit_arg;
 
   if (tmp_table_param.sum_func_count && !group_list)
+  {
     implicit_grouping= TRUE;
+    // Result will contain zero or one row - ordering is meaningless
+    order= NULL;
+  }
 
 #ifdef RESTRICTED_GROUP
   if (implicit_grouping)


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