prestodb/presto · error · SemanticException

CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING

CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING

Error message

%s cannot contain aggregations, window functions or grouping operations: %s

What it means

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.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/Analyzer.java:161

    static void verifyNoAggregateWindowOrGroupingFunctions(
            Map<NodeRef<FunctionCall>, FunctionHandle> functionHandles,
            FunctionAndTypeResolver functionAndTypeResolver,
            Expression predicate,
            String clause)
    {
        List<FunctionCall> aggregates = extractAggregateFunctions(functionHandles, ImmutableList.of(predicate), functionAndTypeResolver);

        List<FunctionCall> windowExpressions = extractWindowFunctions(ImmutableList.of(predicate));

        List<GroupingOperation> groupingOperations = extractExpressions(ImmutableList.of(predicate), GroupingOperation.class);

        List<Expression> found = Stream.concat(
                aggregates.stream(),
                Stream.concat(windowExpressions.stream(), groupingOperations.stream()))
                .collect(toImmutableList());

        if (!found.isEmpty()) {
            throw new SemanticException(CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING, predicate, "%s cannot contain aggregations, window functions or grouping operations: %s", clause, found);
        }
    }

    static void verifyNoExternalFunctions(Map<NodeRef<FunctionCall>, FunctionHandle> functionHandles, FunctionAndTypeResolver functionAndTypeResolver, Expression predicate, String clause)
    {
        List<FunctionCall> externalFunctions = extractExternalFunctions(functionHandles, ImmutableList.of(predicate), functionAndTypeResolver);
        if (!externalFunctions.isEmpty()) {
            throw new SemanticException(NOT_SUPPORTED, predicate, "External functions in %s is not supported: %s", clause, externalFunctions);
        }
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

1. Move the aggregate predicate to HAVING (or a QUALIFY-like construct/window query).
2. Wrap the aggregation in a subquery and filter its result in the outer query.
3. Remove window functions from the predicate and apply filtering in an outer query over the windowed result.
4. Replace grouping() in the predicate with explicit CASE logic on grouped columns.

Example fix

// before
SELECT g, sum(x) FROM t WHERE sum(x) > 10 GROUP BY g;
// after
SELECT g, sum(x) FROM t GROUP BY g HAVING sum(x) > 10;

// reject aggregates/windows/grouping in WHERE-type predicates before running
extractFunctions(predicate).forEach(fn -> {
    if (isAggregate(fn) || isWindowFunction(fn) || fn instanceof GroupingOperation) {
        throw new IllegalArgumentException("Predicate cannot contain " + fn);
    }
});

try { runQuery(sql); } catch (SemanticException e) { if (e.getCode() == CANNOT_HAVE_AGGREGATIONS_WINDOWS_OR_GROUPING) { /* move predicate to HAVING/outer query */ } throw e; }

When it happens

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

Common situations: Trying to filter rows with aggregates in WHERE (classic SQL mistake); adding window functions into filter predicates; copy-pasting HAVING expressions into WHERE.

Related errors

• INVALID_FUNCTION_ARGUMENT
• INVALID_FUNCTION_ARGUMENT
• MUST_BE_AGGREGATE_OR_GROUP_BY
• NESTED_AGGREGATION
• NESTED_WINDOW

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