prestodb/presto · error · SemanticException

NESTED_WINDOW

NESTED_WINDOW

Error message

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

What it means

AggregationAnalyzer.visitFunctionCall rejects window functions (OVER ...) nested inside an aggregate function's arguments. Window functions are computed after aggregation, so appearing inside an aggregate argument is invalid; throws NESTED_WINDOW with the outer aggregate name and inner window calls.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java:404

                    }
                }
                if (!node.getWindow().isPresent()) {
                    List<FunctionCall> aggregateFunctions = extractAggregateFunctions(
                            analysis.getFunctionHandles(),
                            node.getArguments(),
                            functionAndTypeResolver);
                    List<FunctionCall> windowFunctions = extractWindowFunctions(node.getArguments());

                    if (!aggregateFunctions.isEmpty()) {
                        throw new SemanticException(NESTED_AGGREGATION,
                                node,
                                "Cannot nest aggregations inside aggregation '%s': %s",
                                node.getName(),
                                aggregateFunctions);
                    }

                    if (!windowFunctions.isEmpty()) {
                        throw new SemanticException(NESTED_WINDOW,
                                node,
                                "Cannot nest window functions inside aggregation '%s': %s",
                                node.getName(),
                                windowFunctions);
                    }

                    if (node.getOrderBy().isPresent()) {
                        List<Expression> sortKeys = node.getOrderBy().get().getSortItems().stream()
                                .map(SortItem::getSortKey)
                                .collect(toImmutableList());
                        if (node.isDistinct()) {
                            List<FieldId> fieldIds = node.getArguments().stream()
                                    .map(NodeRef::of)
                                    .map(columnReferences::get)
                                    .filter(Objects::nonNull)
                                    .flatMap(Collection::stream)
                                    .collect(toImmutableList());
                            for (Expression sortKey : sortKeys) {

View on GitHub (pinned to 55bb57d202)

Solutions

  1. Compute the window function in a subquery/CTE first, then aggregate its result in the outer query
  2. If you wanted a window over an aggregate, apply the aggregate in GROUP BY and then OVER () on the aggregated value in an outer SELECT
  3. Remove the OVER clause if the inner function was meant to be a plain aggregate argument

Example fix

// before
SELECT sum(row_number() OVER (ORDER BY order_date)) FROM orders
// after
SELECT sum(rn) FROM (SELECT row_number() OVER (ORDER BY order_date) AS rn FROM orders) t
Defensive patterns

Strategy: validation

Validate before calling

// reject window functions inside aggregate arguments before executing
boolean windowInsideAggregate = containsWindowFunctionInsideAggregateCall(expression); // AST walk
if (windowInsideAggregate) throw new IllegalArgumentException("window function inside aggregate");

Try / catch

catch (SemanticException e) { if (e.getCode() == NESTED_WINDOW) { /* hoist window into subquery, aggregate outside */ } throw e; }

Prevention

When it happens

Trigger: Queries like sum(row_number() OVER (PARTITION BY x ORDER BY y)) or count(sum(x) OVER ()), where extractWindowFunctions finds window FunctionCalls among the aggregate's arguments.

Common situations: Attempting to total a running/ranking value; misreading evaluation order of window vs aggregate functions; generated SQL wrapping aggregates around analytic expressions.

Related errors

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