Vladimir Sitnikov created CALCITE-7875:
------------------------------------------
Summary: ORDER BY max(sal) cannot run when a SELECT item max(sal)
is aliased SAL; PostgreSQL resolves sal inside the aggregate to the input column
Key: CALCITE-7875
URL: https://issues.apache.org/jira/browse/CALCITE-7875
Project: Calcite
Issue Type: Improvement
Reporter: Vladimir Sitnikov
A query that orders by an aggregate of a column cannot run in Calcite when a
SELECT item with the same aggregate is aliased with the column's name:
{code:sql}
SELECT max(sal) AS sal, deptno, job
FROM emp
GROUP BY deptno, job
ORDER BY max(sal);
{code}
Calcite resolves {{sal}} inside {{max(...)}} to the SELECT alias {{SAL}}, so
the ORDER BY item becomes {{max(max(sal))}}. On main (1.43.0-SNAPSHOT,
6ff13eaccd) the query passes validation and fails during Enumerable code
generation with {{IllegalStateException: Unable to implement
EnumerableCalc(...)}}; CALCITE-7744 tracks that failure, and its pull request
[https://github.com/apache/calcite/pull/5225] rejects the query in the
validator with {{Aggregate expressions cannot be nested}}. Either way, the
query does not return rows.
PostgreSQL 17.11 runs the query and orders by the maximum salary of each group,
because an output-column name in ORDER BY is recognized only when it stands
alone; inside an expression, {{sal}} is the input column:
{noformat}
sal | deptno | job
-----+--------+-----
50 | 20 | C
200 | 20 | B
300 | 10 | A
(3 rows)
{noformat}
(Data: {{emp(empno int, deptno int, job text, sal numeric)}} with rows
(1,10,'A',100), (2,10,'A',300), (3,20,'B',200), (4,20,'C',50).) The grounding
is another product's behavior, and Calcite deliberately allows SELECT aliases
inside ORDER BY expressions ({{ORDER BY sal + 0}} is valid in Calcite and an
error in PostgreSQL), so adopting PostgreSQL's rule wholesale is not the
request.
*Request.* When an identifier in the argument of a non-windowed aggregate call
in ORDER BY would resolve to a SELECT alias whose expression is itself an
aggregate, resolve it to the input column of the same name, if one exists. Such
an alias can never be valid there: the expansion is always a nested aggregate.
*Acceptance.* The query above validates, converts to a plan with a single
{{MAX(SAL)}} aggregate call, and returns the rows ordered by {{max(sal)}}.
{{ORDER BY sal}} and {{ORDER BY sal + 0}} keep resolving {{sal}} to the alias.
*Out of scope.* Changing how aliases resolve outside aggregate arguments, and
the nested-aggregate rejection that CALCITE-7744 adds for the case where no
input column of that name exists.
*Prior reports.* A JIRA search for ORDER BY alias resolution found no request
for this; the nearest is CALCITE-6082, which fixed an alias that equals a
column name in GROUP BY. The discussion started in
[https://github.com/apache/calcite/pull/5225#issuecomment-5425585810].
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
