{"record":{"id":"7ca90d697e8868b1","repo":"prestodb/presto","slug":"invalid-order-by","errorCode":"INVALID_ORDER_BY","errorMessage":"Window frame of type RANGE PRECEDING or FOLLOWING requires single sort item in ORDER BY (actual: %s)","messagePattern":"Window frame of type RANGE PRECEDING or FOLLOWING requires single sort item in ORDER BY \\(actual: (.+?)\\)","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/ExpressionAnalyzer.java","lineNumber":1317,"sourceCode":"                    return isSimpleKeyEquality(logical.getLeft()) && isSimpleKeyEquality(logical.getRight());\n                }\n            }\n            if (expression instanceof FunctionCall) {\n                FunctionCall func = (FunctionCall) expression;\n                String funcName = func.getName().toString();\n                return funcName.equalsIgnoreCase(\"contains\") || funcName.equalsIgnoreCase(\"presto.default.contains\");\n            }\n            return false;\n        }\n\n        private void analyzeFrameRangeOffset(Expression offsetValue, FrameBound.Type boundType, StackableAstVisitorContext<Context> context, Window window)\n        {\n            if (!window.getOrderBy().isPresent()) {\n                throw new SemanticException(MISSING_ORDER_BY, window, \"Window frame of type RANGE PRECEDING or FOLLOWING requires ORDER BY\");\n            }\n            OrderBy orderBy = window.getOrderBy().get();\n            if (orderBy.getSortItems().size() != 1) {\n                throw new SemanticException(INVALID_ORDER_BY, orderBy, \"Window frame of type RANGE PRECEDING or FOLLOWING requires single sort item in ORDER BY (actual: %s)\", orderBy.getSortItems().size());\n            }\n            Expression sortKey = orderBy.getSortItems().stream().collect(onlyElement()).getSortKey();\n            Type sortKeyType = getExpressionType(sortKey);\n            if (!isNumericType(sortKeyType) && !isDateTimeType(sortKeyType)) {\n                throw new SemanticException(TYPE_MISMATCH, sortKey, \"Window frame of type RANGE PRECEDING or FOLLOWING requires that sort item type be numeric, datetime or interval (actual: %s)\", sortKeyType);\n            }\n\n            Type offsetValueType = process(offsetValue, context);\n\n            if (isNumericType(sortKeyType)) {\n                if (!isNumericType(offsetValueType)) {\n                    throw new SemanticException(TYPE_MISMATCH, offsetValue, \"Window frame RANGE value type (%s) not compatible with sort item type (%s)\", offsetValueType, sortKeyType);\n                }\n            }\n            else { // isDateTimeType(sortKeyType)\n                if (offsetValueType != INTERVAL_DAY_TIME && offsetValueType != INTERVAL_YEAR_MONTH) {\n                    throw new SemanticException(TYPE_MISMATCH, offsetValue, \"Window frame RANGE value type (%s) not compatible with sort item type (%s)\", offsetValueType, sortKeyType);\n                }","sourceCodeStart":1299,"sourceCodeEnd":1335,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/ExpressionAnalyzer.java#L1299-L1335","documentation":"RANGE value offsets are evaluated against the single ORDER BY sort key of the window; comparing offsets against multiple keys is not supported. The analyzer throws INVALID_ORDER_BY when the window has an ORDER BY whose sort item count is not exactly 1 for a RANGE PRECEDING/FOLLOWING frame, and then also requires that key's type to be numeric or datetime/interval.","triggerScenarios":"OVER (ORDER BY a, b RANGE BETWEEN 1 PRECEDING AND CURRENT ROW); any RANGE frame with a value offset where orderBy.getSortItems().size() != 1 (including zero items combined with other checks).","commonSituations":"Wanting a range over a composite key; queries migrated from engines that silently use only the first ORDER BY key; multi-column tie-breaking added alongside a RANGE offset.","solutions":["Reduce the window ORDER BY to a single expression, moving secondary keys into the frame logic or a subquery ordering.","If two keys are essential, switch to ROWS/GROUPS frames or precompute a single comparable key column and ORDER BY it.","For multi-column temporal ranges, concatenate keys into one value (e.g. a normalized timestamp) and use RANGE on that column."],"exampleFix":"// before\nOVER (ORDER BY day, region RANGE BETWEEN 7 PRECEDING AND CURRENT ROW)\n// after\nOVER (PARTITION BY region ORDER BY day RANGE BETWEEN 7 PRECEDING AND CURRENT ROW)","handlingStrategy":"validation","validationCode":"-- ensure single-column ORDER BY for RANGE value frames\n-- bad:  OVER (ORDER BY a, b RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)\n-- good: OVER (ORDER BY a RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Use a single ORDER BY key with RANGE frames","Move secondary ordering to PARTITION BY or subqueries","Also keep the sort key numeric or datetime/interval type"],"tags":["sql","window-functions","range-frame","order-by"],"backgroundTag":"window-frame-requires-order-by","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"}