prestodb/presto · error · SemanticException

MUST_BE_AGGREGATION_FUNCTION

MUST_BE_AGGREGATION_FUNCTION

Error message

Filter is only valid for aggregation functions

What it means

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'.

Source

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

                            }
                        }
                    }

                    // ensure that no output fields are referenced from ORDER BY clause
                    if (orderByScope.isPresent()) {
                        node.getArguments().stream()
                                .forEach(argument -> verifyNoOrderByReferencesToOutputColumns(
                                        argument,
                                        REFERENCE_TO_OUTPUT_ATTRIBUTE_WITHIN_ORDER_BY_AGGREGATION,
                                        "Invalid reference to output projection attribute from ORDER BY aggregation"));
                    }

                    return true;
                }
            }
            else {
                if (node.getFilter().isPresent()) {
                    throw new SemanticException(MUST_BE_AGGREGATION_FUNCTION,
                            node,
                            "Filter is only valid for aggregation functions",
                            node);
                }
                if (node.getOrderBy().isPresent()) {
                    throw new SemanticException(MUST_BE_AGGREGATION_FUNCTION, node, "ORDER BY is only valid for aggregation functions");
                }
            }

            if (node.getWindow().isPresent() && !process(node.getWindow().get(), context)) {
                return false;
            }

            return node.getArguments().stream().allMatch(expression -> process(expression, context));
        }

        @Override
        protected Boolean visitLambdaExpression(LambdaExpression node, Void context)

View on GitHub (pinned to 55bb57d202)

Solutions

  1. Move the condition into the function's arguments or a CASE expression instead of FILTER
  2. Apply FILTER only to aggregate functions (count/sum/avg/...), e.g. sum(CASE WHEN cond THEN x END) as an equivalent
  3. Remove the FILTER clause if it is vestigial
  4. If a custom function should support FILTER semantics, wrap it in an aggregate form or compute via CASE

Example fix

// before
SELECT abs(amount) FILTER (WHERE amount > 0) FROM t
// after
SELECT abs(CASE WHEN amount > 0 THEN amount END) FROM t
Defensive patterns

Strategy: validation

Validate before calling

// only attach FILTER to aggregate functions
if (functionCall.getFilter().isPresent() && !isAggregateFunction(functionCall.getName())) {
    throw new IllegalArgumentException("FILTER only valid on aggregates");
}

Try / catch

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; }

Prevention

When it happens

Trigger: 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.

Common situations: Copy-pasting the COUNT(*) FILTER (WHERE ...) idiom onto scalar functions; UDFs mistakenly assumed to support FILTER; macro/template-generated SQL attaching FILTER broadly.

Related errors

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