{"record":{"id":"f6d3e0c6ff5c21dd","repo":"prestodb/presto","slug":"cannot-have-aggregations-windows-or-grouping","errorCode":"CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING","errorMessage":"%s cannot contain aggregations, window functions or grouping operations: %s","messagePattern":"(.+?) cannot contain aggregations, window functions or grouping operations: (.+?)","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/Analyzer.java","lineNumber":161,"sourceCode":"    static void verifyNoAggregateWindowOrGroupingFunctions(\n            Map<NodeRef<FunctionCall>, FunctionHandle> functionHandles,\n            FunctionAndTypeResolver functionAndTypeResolver,\n            Expression predicate,\n            String clause)\n    {\n        List<FunctionCall> aggregates = extractAggregateFunctions(functionHandles, ImmutableList.of(predicate), functionAndTypeResolver);\n\n        List<FunctionCall> windowExpressions = extractWindowFunctions(ImmutableList.of(predicate));\n\n        List<GroupingOperation> groupingOperations = extractExpressions(ImmutableList.of(predicate), GroupingOperation.class);\n\n        List<Expression> found = Stream.concat(\n                aggregates.stream(),\n                Stream.concat(windowExpressions.stream(), groupingOperations.stream()))\n                .collect(toImmutableList());\n\n        if (!found.isEmpty()) {\n            throw new SemanticException(CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING, predicate, \"%s cannot contain aggregations, window functions or grouping operations: %s\", clause, found);\n        }\n    }\n\n    static void verifyNoExternalFunctions(Map<NodeRef<FunctionCall>, FunctionHandle> functionHandles, FunctionAndTypeResolver functionAndTypeResolver, Expression predicate, String clause)\n    {\n        List<FunctionCall> externalFunctions = extractExternalFunctions(functionHandles, ImmutableList.of(predicate), functionAndTypeResolver);\n        if (!externalFunctions.isEmpty()) {\n            throw new SemanticException(NOT_SUPPORTED, predicate, \"External functions in %s is not supported: %s\", clause, externalFunctions);\n        }\n    }\n}\n","sourceCodeStart":143,"sourceCodeEnd":173,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/Analyzer.java#L143-L173","documentation":"Certain predicates (e.g. HAVING-free WHERE-like clauses or join/scan predicates validated by Analyzer) cannot contain aggregate functions, window functions, or GROUPING operations, because their semantics are evaluated before/during aggregation planning. verifyNoAggregateWindowOrGroupingFunctions collects any such functions found in the predicate and throws CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING listing them.","triggerScenarios":"Putting an aggregate (sum(x)), window function (row_number() OVER ...), or grouping() operation into a clause that forbids them — e.g. a WHERE/join/measure predicate passed to Analyzer with a clause name.","commonSituations":"Trying to filter rows with aggregates in WHERE (classic SQL mistake); adding window functions into filter predicates; copy-pasting HAVING expressions into WHERE.","solutions":["Move the aggregate predicate to HAVING (or a QUALIFY-like construct/window query).","Wrap the aggregation in a subquery and filter its result in the outer query.","Remove window functions from the predicate and apply filtering in an outer query over the windowed result.","Replace grouping() in the predicate with explicit CASE logic on grouped columns."],"exampleFix":"// before\nSELECT g, sum(x) FROM t WHERE sum(x) > 10 GROUP BY g;\n// after\nSELECT g, sum(x) FROM t GROUP BY g HAVING sum(x) > 10;","handlingStrategy":"validation","validationCode":"// reject aggregates/windows/grouping in WHERE-type predicates before running\nextractFunctions(predicate).forEach(fn -> {\n    if (isAggregate(fn) || isWindowFunction(fn) || fn instanceof GroupingOperation) {\n        throw new IllegalArgumentException(\"Predicate cannot contain \" + fn);\n    }\n});","typeGuard":null,"tryCatchPattern":"try { runQuery(sql); } catch (SemanticException e) { if (e.getCode() == CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING) { /* move predicate to HAVING/outer query */ } throw e; }","preventionTips":["Never put aggregates in WHERE — use HAVING.","Filter window-function results in an outer query.","Review generated SQL templates for aggregate leakage into filter clauses."],"tags":["sql","aggregation","where-clause"],"backgroundTag":"aggregate-in-non-aggregate-clause","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"}