prestodb/presto · error · SemanticException

NESTED_WINDOW

NESTED_WINDOW

Error message

Cannot nest window functions inside window function '%s': %s

What it means

Presto forbids nesting window functions inside the arguments, partition/order keys, or frame of another window function. The analyzer extracts window functions from the function's arguments plus window components (partition by, order by, frame); if any are found, this NESTED_WINDOW error is thrown. Window functions cannot take other window functions as inputs.

Source

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

                    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,
                            windowFunctions);
                }

                if (windowFunction.isDistinct()) {
                    throw new SemanticException(NOT_SUPPORTED, node, "DISTINCT in window function parameters not yet supported: %s", windowFunction);
                }

                if (window.getFrame().isPresent()) {
                    analyzeWindowFrame(window.getFrame().get());
                }

                FunctionKind kind = functionAndTypeResolver.getFunctionMetadata(analysis.getFunctionHandle(windowFunction)).getFunctionKind();
                if (kind != AGGREGATE && kind != WINDOW) {
                    throw new SemanticException(MUST_BE_WINDOW_FUNCTION, node, "Not a window function: %s", windowFunction.getName());
                }
            }

View on GitHub (pinned to 55bb57d202)

Solutions

Split into multiple query levels: compute the inner window function in a subquery/CTE, then apply the outer window function over its result.
Rewrite using plain aggregates in inner levels where possible, e.g. aggregate first then rank.
If the inner expression was not meant to be a window function, replace it with a column or aggregate reference.

Example fix

-- before
SELECT sum(rnk) OVER () FROM (SELECT row_number() OVER (ORDER BY x) AS rnk FROM t) -- ok, but the nested form below fails
-- nested (fails): SELECT sum(row_number() OVER (ORDER BY x)) OVER () FROM t;
-- after
WITH r AS (SELECT row_number() OVER (ORDER BY x) AS rnk FROM t)
SELECT sum(rnk) OVER () FROM r;

Defensive patterns

Strategy: fallback

Validate before calling

-- Reject patterns like WINDOW_FN(expr CONTAINING OVER ...) before execution:
-- Parse the SQL (e.g. with sqlparse/SqlParser) and assert no FunctionCall inside another window FunctionCall's args/window has a window specifier.

Try / catch

try {
    query(nestedWindowSql);
} catch (PrestoException e) {
    if ("NESTED_WINDOW".equals(e.getErrorCode().getName())) {
        query(splitIntoCteQuery(nestedWindowSql));
    } else {
        throw e;
    }
}

Prevention

Split multi-stage window computations into CTEs/subqueries: inner window in one level, outer in the next.
Remember each query level in Presto allows one layer of window computation.
When porting analytic SQL, check for window functions nested in arguments, PARTITION BY, ORDER BY, or frame of another.
Add a query-plan/AST lint that flags OVER inside OVER.

When it happens

Trigger: `SELECT sum(row_number() OVER (ORDER BY x)) OVER (PARTITION BY y) FROM t` or `SELECT rank() OVER (PARTITION BY sum(x) OVER ()) FROM t` — extractWindowFunctions on the collected sub-expressions returns a non-empty list.

Common situations: Attempting running-total-of-rank style logic in one query level; copy-pasted analytic SQL from engines with different nesting rules; developers chaining window computations like `lag(sum(...) OVER ())`.

Related errors

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