{"record":{"id":"280c60f1d0996aad","repo":"prestodb/presto","slug":"nested-window-280c60","errorCode":"NESTED_WINDOW","errorMessage":"Cannot nest window functions inside window function '%s': %s","messagePattern":"Cannot nest window functions inside window function '(.+?)': (.+?)","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java","lineNumber":4183,"sourceCode":"                    else {\n                        throw new SemanticException(\n                                WINDOW_FUNCTION_ORDERBY_LITERAL,\n                                node,\n                                \"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\",\n                                windowFunction);\n                    }\n                }\n\n                ImmutableList.Builder<Node> toExtract = ImmutableList.builder();\n                toExtract.addAll(windowFunction.getArguments());\n                toExtract.addAll(window.getPartitionBy());\n                window.getOrderBy().ifPresent(orderBy -> toExtract.addAll(orderBy.getSortItems()));\n                window.getFrame().ifPresent(toExtract::add);\n\n                List<FunctionCall> nestedWindowFunctions = extractWindowFunctions(toExtract.build());\n\n                if (!nestedWindowFunctions.isEmpty()) {\n                    throw new SemanticException(NESTED_WINDOW, node, \"Cannot nest window functions inside window function '%s': %s\",\n                            windowFunction,\n                            windowFunctions);\n                }\n\n                if (windowFunction.isDistinct()) {\n                    throw new SemanticException(NOT_SUPPORTED, node, \"DISTINCT in window function parameters not yet supported: %s\", windowFunction);\n                }\n\n                if (window.getFrame().isPresent()) {\n                    analyzeWindowFrame(window.getFrame().get());\n                }\n\n                FunctionKind kind = functionAndTypeResolver.getFunctionMetadata(analysis.getFunctionHandle(windowFunction)).getFunctionKind();\n                if (kind != AGGREGATE && kind != WINDOW) {\n                    throw new SemanticException(MUST_BE_WINDOW_FUNCTION, node, \"Not a window function: %s\", windowFunction.getName());\n                }\n            }\n","sourceCodeStart":4165,"sourceCodeEnd":4201,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java#L4165-L4201","documentation":"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.","triggerScenarios":"`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.","commonSituations":"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 ())`.","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."],"exampleFix":"-- before\nSELECT sum(rnk) OVER () FROM (SELECT row_number() OVER (ORDER BY x) AS rnk FROM t) -- ok, but the nested form below fails\n-- nested (fails): SELECT sum(row_number() OVER (ORDER BY x)) OVER () FROM t;\n-- after\nWITH r AS (SELECT row_number() OVER (ORDER BY x) AS rnk FROM t)\nSELECT sum(rnk) OVER () FROM r;","handlingStrategy":"fallback","validationCode":"-- Reject patterns like WINDOW_FN(expr CONTAINING OVER ...) before execution:\n-- Parse the SQL (e.g. with sqlparse/SqlParser) and assert no FunctionCall inside another window FunctionCall's args/window has a window specifier.","typeGuard":null,"tryCatchPattern":"try {\n    query(nestedWindowSql);\n} catch (PrestoException e) {\n    if (\"NESTED_WINDOW\".equals(e.getErrorCode().getName())) {\n        query(splitIntoCteQuery(nestedWindowSql));\n    } else {\n        throw e;\n    }\n}","preventionTips":["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."],"tags":["sql","window-function","nesting","semantic-analysis"],"backgroundTag":"nested-window-function","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}