MySQL Bugs: #72512: Non-aggregated query with set function in ORDER BY should be rejected

Bug #72512	Non-aggregated query with set function in ORDER BY should be rejected
Submitted:	2 May 2014 8:57	Modified:	4 Jun 2014 16:45
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This bug report is quite similar to bug#72174, which describes a problem where
the query is a UNION and the ORDER BY contains a set function. However, this
bug applies to a non-aggregated single query block (not a UNION).

According to the SQL standard 2011 Foundation section 7.13 Syntax Rule 28,
a sort specification used in an ORDER BY clause is not allowed to contain a
set function. MySQL does however allow this as an extension for
explicitly and implicitly grouped queries. Thus, if we have a simple table

CREATE TABLE t1(a INTEGER);
INSERT INTO t1 VALUES (1), (2);

MySQL allows a set function in the ORDER BY clause for an explicitly
grouped query:

SELECT a, COUNT(*) FROM t1 GROUP BY a ORDER BY COUNT(*);
+------+----------+
| a    | COUNT(*) |
+------+----------+
|    1 |        1 |
|    2 |        1 |
+------+----------+

For an explicitly grouped query, this extension is useful as it makes it
possible to order on expressions from the grouped table.

MySQL also allows a set function in the ORDER BY clause for an implicitly
grouped query:

SELECT COUNT(*) FROM t1 ORDER BY COUNT(*);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

As an implicitly grouped query never returns more than one row, the
ordering is redundant and can safely be ignored. But this extension to
the standard is harmless as it does not change the properties of the
query specification.

However, consider the non-aggregated query specification:

SELECT a FROM t1;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

An ORDER BY clause can be applied to this query:

SELECT a FROM t1 ORDER BY COUNT(*);
+------+
| a    |
+------+
|    1 |
+------+

Notice that the query specification is transformed to an aggregated query,
even a non-deterministic query, as we pick a random value for the column "a".
Besides, the ORDER BY operation is clearly redundant, as there is never more
than one row to order.

Bu rejecting such queries, queries with confusing semantics will be prevented
and the resolver actions can be simplified, as it will no longer be necessary
to look at the ORDER BY clause to determine whether a query is aggregated.

How to repeat:
CREATE TABLE t1(a INTEGER);
INSERT INTO t1 VALUES (1), (2);

SELECT a FROM t1 ORDER BY COUNT(*);

DROP TABLE t1;

Suggested fix:
Reject such queries.

Hello Roy,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// MySQL 5.5.37

mysql> use test
Database changed
mysql> CREATE TABLE t1(a INTEGER);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT a FROM t1 ORDER BY COUNT(*);
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

// Oracle 11g - rejects

SQL> CREATE TABLE t1(a INTEGER);

Table created.

SQL> INSERT INTO t1 VALUES (1);

1 row created.

SQL> INSERT INTO t1 VALUES (2);

1 row created.

SQL> SELECT * FROM t1;

         A
----------
         1
         2

SQL> SELECT a FROM t1 ORDER BY COUNT(*);
SELECT a FROM t1 ORDER BY COUNT(*)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

SQL> SELECT version FROM V$INSTANCE;

VERSION
-----------------
11.2.0.2.0

SQL>

// PostgreSQL - rejects 

psql (9.3.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

test=# CREATE TABLE t1(a INTEGER);
CREATE TABLE
test=# INSERT INTO t1 VALUES (1);
INSERT 0 1
test=# INSERT INTO t1 VALUES (2);
INSERT 0 1
test=# select * from t1;
 a
---
 1
 2
(2 rows)

test=# SELECT a FROM t1 ORDER BY COUNT(*);
ERROR:  column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT a FROM t1 ORDER BY COUNT(*);
               ^
test=#

// 5.6.19 - affected

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1(a INTEGER);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT a FROM t1 ORDER BY COUNT(*);
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

Noted in 5.7.5 changelog.

A nonaggregated query with an ORDER BY applied to it cannot contain
aggregate functions, but was not rejected as it should be. Now such
queries are rejected with an ER_AGGREGATE_ORDER_NON_AGG_QUERY error.
Example: 

SELECT a FROM t1 ORDER BY COUNT(*);