{"record":{"id":"d93295ea37e9984d","repo":"prestodb/presto","slug":"nondeterministic-order-by-expression-with-select-d","errorCode":"NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT","errorMessage":"Non deterministic ORDER BY expression is not supported with SELECT DISTINCT","messagePattern":"Non deterministic ORDER BY expression is not supported with SELECT DISTINCT","errorType":"validation","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java","lineNumber":5520,"sourceCode":"        }\n\n        private void verifySelectDistinct(QuerySpecification node, List<Expression> outputExpressions)\n        {\n            for (SortItem item : node.getOrderBy().get().getSortItems()) {\n                Expression expression = item.getSortKey();\n\n                if (expression instanceof LongLiteral) {\n                    continue;\n                }\n\n                Expression rewrittenOrderByExpression = ExpressionTreeRewriter.rewriteWith(new OrderByExpressionRewriter(extractNamedOutputExpressions(node.getSelect())), expression);\n                int index = outputExpressions.indexOf(rewrittenOrderByExpression);\n                if (index == -1) {\n                    throw new SemanticException(ORDER_BY_MUST_BE_IN_SELECT, node.getSelect(), \"For SELECT DISTINCT, ORDER BY expressions must appear in select list\");\n                }\n\n                if (!isDeterministic(expression)) {\n                    throw new SemanticException(NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT, expression, \"Non deterministic ORDER BY expression is not supported with SELECT DISTINCT\");\n                }\n            }\n        }\n\n        private List<Expression> analyzeOrderBy(Node node, List<SortItem> sortItems, Scope orderByScope)\n        {\n            ImmutableList.Builder<Expression> orderByFieldsBuilder = ImmutableList.builder();\n\n            for (SortItem item : sortItems) {\n                Expression expression = item.getSortKey();\n\n                if (expression instanceof LongLiteral) {\n                    // this is an ordinal in the output tuple\n\n                    long ordinal = ((LongLiteral) expression).getValue();\n                    if (ordinal < 1 || ordinal > orderByScope.getRelationType().getVisibleFieldCount()) {\n                        throw new SemanticException(INVALID_ORDINAL, expression, \"ORDER BY position %s is not in select list\", ordinal);\n                    }","sourceCodeStart":5502,"sourceCodeEnd":5538,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java#L5502-L5538","documentation":"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.","triggerScenarios":"SELECT DISTINCT x FROM t ORDER BY rand(); or any select-list expression containing a nondeterministic function used for ordering under DISTINCT.","commonSituations":"Attempts to shuffle results randomly; using current_timestamp/now() in ordering; UDFs registered as nondeterministic sneaking into sort keys.","solutions":["Remove nondeterministic functions from the ORDER BY / select expressions under DISTINCT","Generate randomness before aggregation: ORDER BY a deterministic key (e.g. hash of values)","If random sampling is the goal, use TABLESAMPLE or limit after a deterministic sort","Mark/replace UDFs so they are deterministic if they truly are"],"exampleFix":"// before\nSELECT DISTINCT user_id FROM sessions ORDER BY rand();\n// after\nSELECT user_id FROM sessions GROUP BY user_id ORDER BY checksum(user_id);","handlingStrategy":"validation","validationCode":"if (isDistinct && orderByExpressions.stream().anyMatch(this::usesNondeterministicFunction)) {\n    throw new IllegalArgumentException(\"DISTINCT + nondeterministic ORDER BY not supported\");\n}","typeGuard":null,"tryCatchPattern":"try { runQuery(); } catch (SemanticException e) { if (e.getCode() == NONDETERMINISTIC_ORDER_BY_EXPRESSION_WITH_SELECT_DISTINCT.toErrorCode()) { replaceWithDeterministicKey(); } throw e; }","preventionTips":["Never ORDER BY rand()/now() with SELECT DISTINCT","Achieve randomness via TABLESAMPLE or post-query shuffle","Mark custom UDFs with correct determinism metadata"],"tags":["distinct","nondeterministic","order-by"],"backgroundTag":"nondeterministic-order-by-distinct","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}