prestodb/presto · error · SemanticException

INVALID_ORDER_BY

INVALID_ORDER_BY

Error message

Window frame of type RANGE PRECEDING or FOLLOWING requires single sort item in ORDER BY (actual: %s)

What it means

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.

Source

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

                    return isSimpleKeyEquality(logical.getLeft()) && isSimpleKeyEquality(logical.getRight());
                }
            }
            if (expression instanceof FunctionCall) {
                FunctionCall func = (FunctionCall) expression;
                String funcName = func.getName().toString();
                return funcName.equalsIgnoreCase("contains") || funcName.equalsIgnoreCase("presto.default.contains");
            }
            return false;
        }

        private void analyzeFrameRangeOffset(Expression offsetValue, FrameBound.Type boundType, StackableAstVisitorContext<Context> context, Window window)
        {
            if (!window.getOrderBy().isPresent()) {
                throw new SemanticException(MISSING_ORDER_BY, window, "Window frame of type RANGE PRECEDING or FOLLOWING requires ORDER BY");
            }
            OrderBy orderBy = window.getOrderBy().get();
            if (orderBy.getSortItems().size() != 1) {
                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());
            }
            Expression sortKey = orderBy.getSortItems().stream().collect(onlyElement()).getSortKey();
            Type sortKeyType = getExpressionType(sortKey);
            if (!isNumericType(sortKeyType) && !isDateTimeType(sortKeyType)) {
                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);
            }

            Type offsetValueType = process(offsetValue, context);

            if (isNumericType(sortKeyType)) {
                if (!isNumericType(offsetValueType)) {
                    throw new SemanticException(TYPE_MISMATCH, offsetValue, "Window frame RANGE value type (%s) not compatible with sort item type (%s)", offsetValueType, sortKeyType);
                }
            }
            else { // isDateTimeType(sortKeyType)
                if (offsetValueType != INTERVAL_DAY_TIME && offsetValueType != INTERVAL_YEAR_MONTH) {
                    throw new SemanticException(TYPE_MISMATCH, offsetValue, "Window frame RANGE value type (%s) not compatible with sort item type (%s)", offsetValueType, sortKeyType);
                }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Reduce the window ORDER BY to a single expression, moving secondary keys into the frame logic or a subquery ordering.
2. If two keys are essential, switch to ROWS/GROUPS frames or precompute a single comparable key column and ORDER BY it.
3. For multi-column temporal ranges, concatenate keys into one value (e.g. a normalized timestamp) and use RANGE on that column.

Example fix

// before
OVER (ORDER BY day, region RANGE BETWEEN 7 PRECEDING AND CURRENT ROW)
// after
OVER (PARTITION BY region ORDER BY day RANGE BETWEEN 7 PRECEDING AND CURRENT ROW)

-- ensure single-column ORDER BY for RANGE value frames
-- bad:  OVER (ORDER BY a, b RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)
-- good: OVER (ORDER BY a RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)

When it happens

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

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

Related errors

• NESTED_WINDOW
• ORDER_BY_MUST_BE_IN_AGGREGATE
• errorString
• MISSING_ORDER_BY
• WINDOW_FUNCTION_ORDERBY_LITERAL

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