prestodb/presto · error · SemanticException

NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT

NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT

Error message

Non deterministic ORDER BY expression is not supported with SELECT DISTINCT

What it means

Even when a SELECT DISTINCT ORDER BY expression appears in the select list, it must be deterministic; nondeterministic functions (rand(), random(), now()-style) would make the deduplicated ordering unstable, so analysis rejects it.

Source

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

        }

        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

                    long ordinal = ((LongLiteral) expression).getValue();
                    if (ordinal < 1 || ordinal > orderByScope.getRelationType().getVisibleFieldCount()) {
                        throw new SemanticException(INVALID_ORDINAL, expression, "ORDER BY position %s is not in select list", ordinal);
                    }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Remove nondeterministic functions from the ORDER BY / select expressions under DISTINCT
2. Generate randomness before aggregation: ORDER BY a deterministic key (e.g. hash of values)
3. If random sampling is the goal, use TABLESAMPLE or limit after a deterministic sort
4. Mark/replace UDFs so they are deterministic if they truly are

Example fix

// before
SELECT DISTINCT user_id FROM sessions ORDER BY rand();
// after
SELECT user_id FROM sessions GROUP BY user_id ORDER BY checksum(user_id);

if (isDistinct && orderByExpressions.stream().anyMatch(this::usesNondeterministicFunction)) {
    throw new IllegalArgumentException("DISTINCT + nondeterministic ORDER BY not supported");
}

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

When it happens

Trigger: SELECT DISTINCT x FROM t ORDER BY rand(); or any select-list expression containing a nondeterministic function used for ordering under DISTINCT.

Common situations: Attempts to shuffle results randomly; using current_timestamp/now() in ordering; UDFs registered as nondeterministic sneaking into sort keys.

Related errors

• ORDER_BY_MUST_BE_IN_AGGREGATE
• ORDER_BY_MUST_BE_IN_SELECT
• CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION
• errorString
• INVALID_ORDER_BY

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