{"record":{"id":"13480c46ec1a96cd","repo":"prestodb/presto","slug":"must-be-aggregation-function","errorCode":"MUST_BE_AGGREGATION_FUNCTION","errorMessage":"Filter is only valid for aggregation functions","messagePattern":"Filter is only valid for aggregation functions","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java","lineNumber":457,"sourceCode":"                            }\n                        }\n                    }\n\n                    // ensure that no output fields are referenced from ORDER BY clause\n                    if (orderByScope.isPresent()) {\n                        node.getArguments().stream()\n                                .forEach(argument -> verifyNoOrderByReferencesToOutputColumns(\n                                        argument,\n                                        REFERENCE_TO_OUTPUT_ATTRIBUTE_WITHIN_ORDER_BY_AGGREGATION,\n                                        \"Invalid reference to output projection attribute from ORDER BY aggregation\"));\n                    }\n\n                    return true;\n                }\n            }\n            else {\n                if (node.getFilter().isPresent()) {\n                    throw new SemanticException(MUST_BE_AGGREGATION_FUNCTION,\n                            node,\n                            \"Filter is only valid for aggregation functions\",\n                            node);\n                }\n                if (node.getOrderBy().isPresent()) {\n                    throw new SemanticException(MUST_BE_AGGREGATION_FUNCTION, node, \"ORDER BY is only valid for aggregation functions\");\n                }\n            }\n\n            if (node.getWindow().isPresent() && !process(node.getWindow().get(), context)) {\n                return false;\n            }\n\n            return node.getArguments().stream().allMatch(expression -> process(expression, context));\n        }\n\n        @Override\n        protected Boolean visitLambdaExpression(LambdaExpression node, Void context)","sourceCodeStart":439,"sourceCodeEnd":475,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/AggregationAnalyzer.java#L439-L475","documentation":"FILTER (WHERE ...) and ORDER BY inside a function call are only meaningful for aggregate functions. When AggregationAnalyzer.visitFunctionCall encounters a non-aggregate (non-window-eligible) function carrying a FILTER clause, it throws MUST_BE_AGGREGATION_FUNCTION with 'Filter is only valid for aggregation functions'.","triggerScenarios":"Writing e.g. abs(x) FILTER (WHERE x > 0), or adding FILTER to a scalar/UDF function inside an aggregated SELECT; the node has getFilter().isPresent() but is not an aggregation.","commonSituations":"Copy-pasting the COUNT(*) FILTER (WHERE ...) idiom onto scalar functions; UDFs mistakenly assumed to support FILTER; macro/template-generated SQL attaching FILTER broadly.","solutions":["Move the condition into the function's arguments or a CASE expression instead of FILTER","Apply FILTER only to aggregate functions (count/sum/avg/...), e.g. sum(CASE WHEN cond THEN x END) as an equivalent","Remove the FILTER clause if it is vestigial","If a custom function should support FILTER semantics, wrap it in an aggregate form or compute via CASE"],"exampleFix":"// before\nSELECT abs(amount) FILTER (WHERE amount > 0) FROM t\n// after\nSELECT abs(CASE WHEN amount > 0 THEN amount END) FROM t","handlingStrategy":"validation","validationCode":"// only attach FILTER to aggregate functions\nif (functionCall.getFilter().isPresent() && !isAggregateFunction(functionCall.getName())) {\n    throw new IllegalArgumentException(\"FILTER only valid on aggregates\");\n}","typeGuard":null,"tryCatchPattern":"catch (SemanticException e) { if (e.getCode() == MUST_BE_AGGREGATION_FUNCTION && e.getMessage().contains(\"Filter is only valid\")) { /* rewrite as CASE WHEN or move FILTER to an aggregate */ } throw e; }","preventionTips":["Use FILTER (WHERE ...) only with count/sum/avg/min/max and similar aggregates","Express conditional scalar logic with CASE WHEN","Document that UDFs do not support FILTER","Grep templates for FILTER usage and verify each target is an aggregate"],"tags":["sql","filter-clause","aggregation","semantic-analysis","presto"],"backgroundTag":"filter-on-non-aggregate","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"}