SQL: ORDER BY column and aggregate orders only by aggregate #50355
Description
Query to test on SQL's test data: SELECT gender, MIN(salary) AS min, COUNT(*) AS c, MAX(salary) FROM test_emp GROUP BY gender HAVING c > 1 ORDER BY gender ASC, MAX(salary) DESC which gives:
gender | min | c | MAX(salary)
---------------+---------------+---------------+---------------
M |25945.0 |57 |74999.0
F |25976.0 |33 |74572.0
null |25324.0 |10 |73717.0
The result seems to indicate that the ordering is done ONLY on the aggregate. Changing the directions of ORDER BYs - SELECT gender, MIN(salary) AS min, COUNT(*) AS c, MAX(salary) FROM test_emp GROUP BY gender HAVING c > 1 ORDER BY gender DESC, MAX(salary) ASC seems to support the above assumption:
gender | min | c | MAX(salary)
---------------+---------------+---------------+---------------
null |25324.0 |10 |73717.0
F |25976.0 |33 |74572.0
M |25945.0 |57 |74999.0
Our tests DO cover this scenario but only partially.
In agg-ordering.sql-spec we have
SELECT gender, MIN(salary) AS min, COUNT(*) AS c FROM test_emp GROUP BY gender HAVING c > 1 ORDER BY gender, MAX(salary)
which runs ok, but only because ordering by gender and ordering by MAX(salary) offer the same results for ASC ordering. A better test should also return MAX(salary) as a projection and should mix ASC and DESC as directions of ORDER BY.