prestodb/presto · error · SemanticException

ORDER_BY_MUST_BE_IN_SELECT

ORDER_BY_MUST_BE_IN_SELECT

Error message

For SELECT DISTINCT, ORDER BY expressions must appear in select list

What it means

With SELECT DISTINCT, every ORDER BY expression must appear in the SELECT list, because ordering by a value not present in the deduplicated output is ill-defined. Analysis rewrites each ORDER BY expression against output names and fails if it is not found.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:5516

            if (rowCount < 0) {
                throw new SemanticException(INVALID_OFFSET_ROW_COUNT, node, "OFFSET row count must be greater or equal to 0 (actual value: %s)", rowCount);
            }
            analysis.setOffset(node, rowCount);
        }

        private void verifySelectDistinct(QuerySpecification node, List<Expression> outputExpressions)
        {
            for (SortItem item : node.getOrderBy().get().getSortItems()) {
                Expression expression = item.getSortKey();

                if (expression instanceof LongLiteral) {
                    continue;
                }

                Expression rewrittenOrderByExpression = ExpressionTreeRewriter.rewriteWith(new OrderByExpressionRewriter(extractNamedOutputExpressions(node.getSelect())), expression);
                int index = outputExpressions.indexOf(rewrittenOrderByExpression);
                if (index == -1) {
                    throw new SemanticException(ORDER_BY_MUST_BE_IN_SELECT, node.getSelect(), "For SELECT DISTINCT, ORDER BY expressions must appear in select list");
                }

                if (!isDeterministic(expression)) {
                    throw new SemanticException(NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT, expression, "Non deterministic ORDER BY expression is not supported with SELECT DISTINCT");
                }
            }
        }

        private List<Expression> analyzeOrderBy(Node node, List<SortItem> sortItems, Scope orderByScope)
        {
            ImmutableList.Builder<Expression> orderByFieldsBuilder = ImmutableList.builder();

            for (SortItem item : sortItems) {
                Expression expression = item.getSortKey();

                if (expression instanceof LongLiteral) {
                    // this is an ordinal in the output tuple

View on GitHub (pinned to 55bb57d202)

Solutions

Add the ORDER BY expression to the SELECT list
Order by an aliased output column (ORDER BY alias)
Drop DISTINCT if full-row dedup with external sort is intended (restructure query)
Wrap in an outer query: inner SELECT DISTINCT, outer ORDER BY

Example fix

// before
SELECT DISTINCT department FROM employees ORDER BY salary;
// after
SELECT DISTINCT department, salary FROM employees ORDER BY salary;

Defensive patterns

Strategy: validation

Validate before calling

if (isDistinct && orderByExpressions.stream().noneMatch(selectList::contains)) {
    throw new IllegalArgumentException("ORDER BY expressions must appear in SELECT list for DISTINCT");
}

Try / catch

try { runQuery(); } catch (SemanticException e) { if (e.getCode() == ORDER_BY_MUST_BE_IN_SELECT.toErrorCode()) { addSortKeyToSelectAndRetry(); } throw e; }

Prevention

When adding DISTINCT, audit existing ORDER BY clauses
Order by select-list aliases only
Use outer-query ordering when dedup + external sort is needed

When it happens

Trigger: SELECT DISTINCT a FROM t ORDER BY b; or ORDER BY expression like ORDER BY a+1 when only a is selected.

Common situations: Adding DISTINCT to an existing query with pre-existing ORDER BY; users sorting by a hidden column; ORM-generated queries mixing distinct with sort keys.

Related errors

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