From: Roy Lyseng
Date: January 21 2011 3:03pm
Subject: Re: bzr commit into mysql-trunk branch (jorgen.loland:3520) Bug#47853
List-Archive: http://lists.mysql.com/commits/129350
Message-Id: <4D39A048.8020505@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hi Jørgen,

thanks for fixing this problem.

The bugfix is approved. I have some comments to your commit description and a 
refactoring suggestion, but nothing that you need to apply.

Thanks,
Roy

On 17.01.11 13.19, Jorgen Loland wrote:
> #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.
The first sentence is not entirely correct: According to the SQL standard, a 
query is grouped if it contains a GROUP BY and/or a HAVING clause. Hence, a 
query can be implicitly grouped only if contains neither GROUP BY nor HAVING. 
MySQL does however implement an extension: If the query contains a HAVING clause 
but no GROUP BY clause, and the HAVING clause does not contain any aggregated 
expressions, then the HAVING clause is treated as a WHERE clause and the query 
is not grouped. But this is a pure extension, as a standard SQL HAVING clause 
would not allow any non-aggregated column references.

I do not think this has any practical implications here, though.
>
>        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

This is correct. But if implicit_grouping had been defined correctly, and not 
included queries with only a HAVING clause, you could have written this:

Result will contain exactly one row - ordering is meaningless

> +    order= NULL;
> +  }
>
>   #ifdef RESTRICTED_GROUP
>     if (implicit_grouping)