prestodb/presto · error · SemanticException

WINDOW_FUNCTION_ORDERBY_LITERAL

WINDOW_FUNCTION_ORDERBY_LITERAL

Error message

ORDER BY literals/constants with window function: '%s' is unnecessary and expensive. If you intend to ORDER BY using ordinals, please use the actual expression instead of the ordinal

What it means

When a window function's OVER clause contains ORDER BY items that are plain literals/constants, Presto flags this as unnecessary and expensive. If the session/system property allow-window-order-by-literals is disabled (default), this WINDOW_FUNCTION_ORDERBY_LITERAL error is thrown; if enabled, it is downgraded to a performance warning.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:4166

                    throw new SemanticException(NOT_SUPPORTED, node, "FILTER is not yet supported for window functions");
                }

                if (windowFunction.getOrderBy().isPresent()) {
                    throw new SemanticException(NOT_SUPPORTED, windowFunction, "Window function with ORDER BY is not supported");
                }

                Window window = windowFunction.getWindow().get();
                if (window.getOrderBy().filter(orderBy -> orderBy.getSortItems().stream().anyMatch(item -> item.getSortKey() instanceof Literal)).isPresent()) {
                    if (isAllowWindowOrderByLiterals(session)) {
                        warningCollector.add(
                                new PrestoWarning(
                                        PERFORMANCE_WARNING,
                                        String.format(
                                                "ORDER BY literals/constants with window function: '%s' is unnecessary and expensive. If you intend to ORDER BY using ordinals, please use the actual expression instead of the ordinal",
                                                windowFunction)));
                    }
                    else {
                        throw new SemanticException(
                                WINDOW_FUNCTION_ORDERBY_LITERAL,
                                node,
                                "ORDER BY literals/constants with window function: '%s' is unnecessary and expensive. If you intend to ORDER BY using ordinals, please use the actual expression instead of the ordinal",
                                windowFunction);
                    }
                }

                ImmutableList.Builder<Node> toExtract = ImmutableList.builder();
                toExtract.addAll(windowFunction.getArguments());
                toExtract.addAll(window.getPartitionBy());
                window.getOrderBy().ifPresent(orderBy -> toExtract.addAll(orderBy.getSortItems()));
                window.getFrame().ifPresent(toExtract::add);

                List<FunctionCall> nestedWindowFunctions = extractWindowFunctions(toExtract.build());

                if (!nestedWindowFunctions.isEmpty()) {
                    throw new SemanticException(NESTED_WINDOW, node, "Cannot nest window functions inside window function '%s': %s",
                            windowFunction,

View on GitHub (pinned to 55bb57d202)

Solutions

Remove the literal ORDER BY items from the OVER clause — they don't define a meaningful order.
Replace the ordinal with the actual column expression you meant to sort by, e.g. `OVER (ORDER BY col1)`.
As a temporary measure, enable the allow-window-order-by-literals config/session property to downgrade it to a warning, but fix the query afterwards.

Example fix

-- before
SELECT row_number() OVER (ORDER BY 1) FROM t;
-- after
SELECT row_number() OVER (ORDER BY created_at) FROM t;

Defensive patterns

Strategy: validation

Validate before calling

-- Detect literal sort keys in OVER(ORDER BY ...) before execution:
-- regex: OVER\s*\(\s*ORDER\s+BY\s+(\d+|'[^']*'|NULL)  → remove or replace with a real column expression.

Prevention

Never write ORDER BY <constant> inside an OVER clause.
If you meant ordinal ordering, write the actual column expression instead.
Enable allow-window-order-by-literals only as a transitional warning setting, with a follow-up fix ticket.
Lint analytic SQL for constant sort keys in code review or CI.

When it happens

Trigger: `SELECT row_number() OVER (ORDER BY 1) FROM t` — any windowFunction whose window's ORDER BY sort items have a Literal sort key, analyzed while isAllowWindowOrderByLiterals(session) is false.

Common situations: Copy-pasted SQL from engines that accept `ORDER BY 1` ordinals in OVER clauses; hand-written queries where a developer intended positional ordering; ORM/BI tools generating constant sort keys.

Related errors

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