prestodb/presto · error · SemanticException

MISSING_ORDER_BY

MISSING_ORDER_BY

Error message

Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY

What it means

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.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/ExpressionAnalyzer.java:1046

                            if (!type.equals(INTEGER) && !type.equals(BIGINT)) {
                                throw new SemanticException(TYPE_MISMATCH, node, "Window frame ROWS end value type must be INTEGER or BIGINT (actual %s)", type);
                            }
                        }
                    }
                    else if (frame.getType() == RANGE) {
                        if (frame.getStart().getValue().isPresent()) {
                            Expression startValue = frame.getStart().getValue().get();
                            analyzeFrameRangeOffset(startValue, frame.getStart().getType(), context, window);
                        }
                        if (frame.getEnd().isPresent() && frame.getEnd().get().getValue().isPresent()) {
                            Expression endValue = frame.getEnd().get().getValue().get();
                            analyzeFrameRangeOffset(endValue, frame.getEnd().get().getType(), context, window);
                        }
                    }
                    else if (frame.getType() == GROUPS) {
                        if (frame.getStart().getValue().isPresent()) {
                            if (!window.getOrderBy().isPresent()) {
                                throw new SemanticException(MISSING_ORDER_BY, window, "Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY");
                            }
                            Expression startValue = frame.getStart().getValue().get();
                            Type type = process(startValue, context);
                            if (!type.equals(INTEGER) && !type.equals(BIGINT)) {
                                throw new SemanticException(TYPE_MISMATCH, node, "Window frame GROUPS start value type must be INTEGER or BIGINT (actual %s)", type);
                            }
                        }
                        if (frame.getEnd().isPresent() && frame.getEnd().get().getValue().isPresent()) {
                            if (!window.getOrderBy().isPresent()) {
                                throw new SemanticException(MISSING_ORDER_BY, window, "Window frame of type GROUPS PRECEDING or FOLLOWING requires ORDER BY");
                            }
                            Expression endValue = frame.getEnd().get().getValue().get();
                            Type type = process(endValue, context);
                            if (!type.equals(INTEGER) && !type.equals(BIGINT)) {
                                throw new SemanticException(TYPE_MISMATCH, node, "Window frame GROUPS end value type must be INTEGER or BIGINT (actual %s)", type);
                            }
                        }
                    }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Add an ORDER BY clause to the window definition, e.g. OVER (ORDER BY col GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW).
2. If ordering is irrelevant, switch the frame back to ROWS or use UNBOUNDED/CURRENT ROW bounds which do not require ORDER BY.
3. Rewrite the window function using an aggregate-with-GROUP BY if peer-group semantics are not actually needed.

Example fix

// before
SELECT sum(v) OVER (GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t
// after
SELECT sum(v) OVER (ORDER BY ts GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t

-- check before running: any GROUPS frame with value bounds must have ORDER BY
-- SELECT ... OVER (ORDER BY col GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW)
-- reject queries matching regex: GROUPS\s+BETWEEN\s+(?!CURRENT\s+ROW|UNBOUNDED) when no ORDER BY present in the OVER clause

When it happens

Trigger: 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).

Common situations: 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.

Related errors

• NESTED_WINDOW
• WINDOW_REQUIRES_OVER
• MUST_BE_AGGREGATE_OR_GROUP_BY
• NESTED_AGGREGATION
• MUST_BE_AGGREGATION_FUNCTION

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