MySQL Bugs: #72174: UNION with set function in ORDER BY clause should be rejected

Bug #72174	UNION with set function in ORDER BY clause should be rejected
Submitted:	31 Mar 2014 19:58	Modified:	4 Jun 2014 16:46
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	order by, set function, UNION

Description:
A UNION query with an ORDER BY clause containing a set function is allowed
by the MySQL server, even though such queries are invalid according to the
SQL standard.

The result of this simple UNION query:

SELECT 1 AS foo
UNION
SELECT 2;

is:
+-----+
| foo |
+-----+
|   1 |
|   2 |
+-----+

We can add an ORDER BY clause with a set function like this:

SELECT 1 AS foo
UNION
SELECT 2
ORDER BY MAX(1);

Apparently, addition of ORDER BY causes the query to become aggregated,
and the value of "foo" is the first occurrence from the UNION:

+-----+
| foo |
+-----+
|   1 |
+-----+

Notice that this construct is non-deterministic:

SELECT 2 AS foo
UNION
SELECT 1
ORDER BY MAX(1);

returns a different result:

SELECT 1 AS foo
UNION
SELECT 2
ORDER BY MAX(1);

It is hard to imagine why ORDER BY should cause a query to become aggregated.
If we really want to aggregate the result of a UNION, this is possible with a
standard-compliant query:

SELECT COUNT(*) AS c, MAX(foo) AS m FROM
   (SELECT 1 AS foo
    UNION
    SELECT 2
    ) AS u;

giving this result:

+---+------+
| c | m    |
+---+------+
| 2 |    2 |
+---+------+

It is of course possible to apply an ORDER BY clause to this query, although
it does not make much sense to order an aggregated query with only one row:

SELECT COUNT(*) AS c, MAX(foo) AS m FROM
   (SELECT 1 AS foo
    UNION
    SELECT 2
    ) AS u
ORDER BY c;

The result is the same as for the above query:
+---+------+
| c | m    |
+---+------+
| 2 |    2 |
+---+------+

How to repeat:
SELECT 1 AS foo
UNION
SELECT 2
ORDER BY MAX(1);

Suggested fix:
Give error message if such query is encountered

The result of

SELECT 2 AS foo
UNION
SELECT 1
ORDER BY MAX(1);

should be

+-----+
| foo |
+-----+
|   2 |
+-----+

Hello Roy,

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

Thanks,
Umesh

Noted in 5.7.5 changelog.

UNION queries with an aggregate function in an ORDER BY clause were
not rejected as they should be. Now such queries are rejected with an
ER_AGGREGATE_ORDER_FOR_UNION error. Example:

SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);