prestodb/presto · error · SemanticException

errorString

Error message

errorString

What it means

verifyNoOrderByReferencesToOutputColumns throws a SemanticException with the caller-supplied errorCode and errorString when an ORDER BY clause references an output-column alias/scope that it is not allowed to reference. It finds any expression in the ORDER BY that resolves against the orderByScope and throws using the caller's message template, so the exact text depends on the call site (e.g. 'ORDER BY clause cannot contain aggregations...' style errors).

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java:739

                    && !hasFreeReferencesToLambdaArgument(node, analysis)) {
                return true;
            }

            return super.process(node, context);
        }
    }

    private boolean hasOrderByReferencesToOutputColumns(Node node)
    {
        return hasReferencesToScope(node, analysis, orderByScope.get());
    }

    private void verifyNoOrderByReferencesToOutputColumns(Node node, SemanticErrorCode errorCode, String errorString)
    {
        getReferencesToScope(node, analysis, orderByScope.get())
                .findFirst()
                .ifPresent(expression -> {
                    throw new SemanticException(errorCode, expression, errorString);
                });
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

1. Rewrite the ORDER BY to reference the underlying column expressions instead of the disallowed output reference.
2. Remove the aggregate/window/grouping construct from the ORDER BY expression.
3. Add the ordered expression to the GROUP BY clause and order by the base column.
4. Use ordinal or a subquery to control ordering without referencing the forbidden scope.

Example fix

// before
SELECT count(*) AS c FROM t GROUP BY g ORDER BY c + 1;
// after
SELECT count(*) AS c FROM t GROUP BY g ORDER BY count(*) + 1;

// ensure ORDER BY references base columns, not forbidden output-scope aliases
orderByExpressions.forEach(e -> {
    if (isOutputColumnAlias(e) && disallowedContext) {
        throw new IllegalArgumentException("ORDER BY references forbidden output column: " + e);
    }
});

try { runQuery(sql); } catch (SemanticException e) { if (e.getMessage().contains("ORDER BY")) { /* rewrite ORDER BY to base columns */ } throw e; }

When it happens

Trigger: An ORDER BY (of a grouped query, aggregation query, or similar context validated via this helper) references expressions resolved from the output-column scope where that is prohibited — e.g. ORDER BY on output aliases containing aggregates when the context disallows it.

Common situations: Ordering by output column aliases after adding aggregates/window functions; refactoring a query so an alias becomes an aggregation while ORDER BY still resolves it.

Related errors

• ORDER_BY_MUST_BE_IN_AGGREGATE
• INVALID_ORDER_BY
• WINDOW_FUNCTION_ORDERBY_LITERAL
• AMBIGUOUS_ATTRIBUTE
• INVALID_TABLE_PROPERTY

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/05fbacf990d15775. Report an issue: GitHub.