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)
