Bug #105163 Incorrect ordering by an expression which includes output column
Submitted: 7 Oct 2021 8:46 Modified: 7 Oct 2021 12:56
Reporter: Владислав Сокол Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any

[7 Oct 2021 8:46] Владислав Сокол
Description:
When ORDER BY expression is an expression which includes aggregated output column the output ordering may be incorrect.

DEMO: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ff973ebf935e195e0e8fd0a268ec2edf

The output sorting is incorrect (rows are pronted in DESC order).

How to repeat:
CREATE TABLE test (id INT, val INT);

INSERT INTO test VALUES
(1,1),
(1,2),
(2,3),
(2,4);

SELECT id, AVG(val) AS average, 1 - AVG(val) ordering_expression
FROM test
GROUP BY id
ORDER BY 1 - average;

Output (DESC sorting is observed):

> id | average | ordering_expression
> -: | ------: | ------------------:
>  1 |  1.5000 |             -0.5000
>  2 |  3.5000 |             -2.5000

Suggested fix:
Use output column expression instead of the output name in ordering expression.

SELECT id, AVG(val) AS average, 1 - AVG(val) ordering_expression
FROM test
GROUP BY id
ORDER BY 1 - AVG(val);

Output (correct sorting):

> id | average | ordering_expression
> -: | ------: | ------------------:
>  2 |  3.5000 |             -2.5000
>  1 |  1.5000 |             -0.5000
[7 Oct 2021 13:13] MySQL Verification Team
HI Mr. Sokol,

Thank you for your bug report.

However, it is not a bug.

The aggregated SELECT statements do not allow expressions in the ORDER BY clause. This is mentioned in our Reference Manual:

"
Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1.
"

Not a bug.
[8 Oct 2021 8:53] Øystein Grøvlen
If expressions were not allowed, I would think the query should cause a syntax error.  However, according to the manual, an ORDER BY expression can have the following syntax:

 [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]

So it seems expressions are allowed in ORDER BY statements.  The cited statement just says how columns may be referred to.  If 1 is to be interpreted as column position, even in expressions, it seems to output order is still wrong.