{"record":{"id":"d40c2be20b6f2278","repo":"prestodb/presto","slug":"missing-order-by","errorCode":"MISSING_ORDER_BY","errorMessage":"Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY","messagePattern":"Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/ExpressionAnalyzer.java","lineNumber":1046,"sourceCode":"                            if (!type.equals(INTEGER) && !type.equals(BIGINT)) {\n                                throw new SemanticException(TYPE_MISMATCH, node, \"Window frame ROWS end value type must be INTEGER or BIGINT (actual %s)\", type);\n                            }\n                        }\n                    }\n                    else if (frame.getType() == RANGE) {\n                        if (frame.getStart().getValue().isPresent()) {\n                            Expression startValue = frame.getStart().getValue().get();\n                            analyzeFrameRangeOffset(startValue, frame.getStart().getType(), context, window);\n                        }\n                        if (frame.getEnd().isPresent() && frame.getEnd().get().getValue().isPresent()) {\n                            Expression endValue = frame.getEnd().get().getValue().get();\n                            analyzeFrameRangeOffset(endValue, frame.getEnd().get().getType(), context, window);\n                        }\n                    }\n                    else if (frame.getType() == GROUPS) {\n                        if (frame.getStart().getValue().isPresent()) {\n                            if (!window.getOrderBy().isPresent()) {\n                                throw new SemanticException(MISSING_ORDER_BY, window, \"Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY\");\n                            }\n                            Expression startValue = frame.getStart().getValue().get();\n                            Type type = process(startValue, context);\n                            if (!type.equals(INTEGER) && !type.equals(BIGINT)) {\n                                throw new SemanticException(TYPE_MISMATCH, node, \"Window frame GROUPS start value type must be INTEGER or BIGINT (actual %s)\", type);\n                            }\n                        }\n                        if (frame.getEnd().isPresent() && frame.getEnd().get().getValue().isPresent()) {\n                            if (!window.getOrderBy().isPresent()) {\n                                throw new SemanticException(MISSING_ORDER_BY, window, \"Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY\");\n                            }\n                            Expression endValue = frame.getEnd().get().getValue().get();\n                            Type type = process(endValue, context);\n                            if (!type.equals(INTEGER) && !type.equals(BIGINT)) {\n                                throw new SemanticException(TYPE_MISMATCH, node, \"Window frame GROUPS end value type must be INTEGER or BIGINT (actual %s)\", type);\n                            }\n                        }\n                    }","sourceCodeStart":1028,"sourceCodeEnd":1064,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/ExpressionAnalyzer.java#L1028-L1064","documentation":"Presto throws this during window-function analysis when a window frame of type GROUPS uses a value bound (n PRECEDING / n FOLLOWING) on the frame start but the window specification has no ORDER BY clause. GROUPS-mode frames count peer groups, and peer groups are only defined relative to an ordering, so without ORDER BY the frame is meaningless and the analyzer rejects the query at analysis time.","triggerScenarios":"Any query with OVER (... ROWS/GROUPS ... ) where frame type is GROUPS and frame.getStart().getValue() is present (a value bound, not UNBOUNDED/CURRENT ROW) while window.getOrderBy() is absent. E.g. SELECT sum(x) OVER (ORDER BY omitted ... GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW).","commonSituations":"Developers converting ROWS frames to GROUPS frames after upgrading to Presto versions supporting GROUPS; the old query had no ORDER BY and ROWS tolerated it, but GROUPS does not. Also hand-written window specs where ORDER BY was removed during refactoring.","solutions":["Add an ORDER BY clause to the window definition, e.g. OVER (ORDER BY col GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW).","If ordering is irrelevant, switch the frame back to ROWS or use UNBOUNDED/CURRENT ROW bounds which do not require ORDER BY.","Rewrite the window function using an aggregate-with-GROUP BY if peer-group semantics are not actually needed."],"exampleFix":"// before\nSELECT sum(v) OVER (GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t\n// after\nSELECT sum(v) OVER (ORDER BY ts GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t","handlingStrategy":"validation","validationCode":"-- check before running: any GROUPS frame with value bounds must have ORDER BY\n-- SELECT ... OVER (ORDER BY col GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)\n-- reject queries matching regex: GROUPS\\s+BETWEEN\\s+(?!CURRENT\\s+ROW|UNBOUNDED) when no ORDER BY present in the OVER clause","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Always pair GROUPS frames with an explicit ORDER BY","Never convert ROWS frames to GROUPS without reviewing the window's ORDER BY","Keep window frame and ORDER BY clauses adjacent in generated SQL templates"],"tags":["sql","window-functions","groups-frame","semantic-analysis"],"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"}