Bug #47853 Implicitly grouped queries do not need to use temporary tables

• Submitted: 6 Oct 2009 9:05
• Modified: 24 Jan 2011 15:16
• Reporter: Jørgen Løland
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.1, 6.0
• OS: Any
• Assigned to: Jørgen Løland
• CPU Architecture: Any

[6 Oct 2009 9:05] Jørgen Løland

Description:
When processing queries like:

SELECT MAX(pk) as max, i
FROM t1
ORDER BY MAX(i);

The optimizer chooses to use temporary tables because the ORDER BY clause is considered complex:

EXPLAIN
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

EXPLAIN
SELECT MAX(pk) as max, i
FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	

However, since this query contains aggregate functions and no GROUP BY clause, the result will always contain zero or one row. Hence, the ordering will be trivial.

How to repeat:
CREATE TABLE t1 (
  pk INT NOT NULL,
  i INT,
  PRIMARY KEY (pk)
);
INSERT INTO t1 VALUES (1,11),(2,12),(3,13);

EXPLAIN
SELECT MAX(pk) as max, i
FROM t1
ORDER BY max;

EXPLAIN
SELECT MAX(pk) as max, i
FROM t1;

Suggested fix:
Either:
 1) Remove the order list completely if there is implicit grouping, or
 2) Make the optimizer choose to not use temporary tables if there is implicit 
    grouping

[17 Jan 2011 12:20] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128947

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.

[24 Jan 2011 9:02] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/129407

3544 Jorgen Loland	2011-01-24
      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.

[24 Jan 2011 9:02] Bugs System

Pushed into mysql-trunk 5.6.2 (revid:[e-mail]-20110124090203-1grig6b522tmw4cf) (version source revid:[e-mail]-20110124090203-1grig6b522tmw4cf) (merge vers: 5.6.2) (pib:24)

[24 Jan 2011 15:16] Paul DuBois

Noted in 5.6.2 changelog.

A query that contained an aggregate function but no GROUP BY clause
was implicitly grouped. But implicitly grouped queries return zero or
one row, so ordering does not make sense.

[4 Mar 2016 16:41] Paul DuBois

Revised changelog entry:

A query that contains an aggregate function but no GROUP BY clause is
implicitly grouped. If such a query also contained an ORDER BY
clause, the optimizer could choose to use a temporary table to
perform the ordering. This is unnecessary because implicitly grouped
queries return at most one row and need no ordering.