apache/cassandra · error · InvalidRequestException

Aggregation function are not supported in the where clause

Error message

Aggregation function are not supported in the where clause

What it means

In FunctionCall.Raw.prepare(), after resolving the function, if fun.isAggregate() the parser rejects it: aggregate functions (sum, count, avg, etc.) cannot appear in this position (WHERE-clause terms / scalar term contexts). Only scalar functions may be used here.

Source

Thrown at src/java/org/apache/cassandra/cql3/functions/FunctionCall.java:164

        public static Raw newNegation(Term.Raw raw)
        {
            FunctionName name = FunctionName.nativeFunction(OperationFcts.NEGATION_FUNCTION_NAME);
            return new Raw(name, Collections.singletonList(raw));
        }

        public static Raw newCast(Term.Raw raw, CQL3Type type)
        {
            FunctionName name = FunctionName.nativeFunction(CastFcts.getFunctionName(type));
            return new Raw(name, Collections.singletonList(raw));
        }

        public Term prepare(String keyspace, ColumnSpecification receiver) throws InvalidRequestException
        {
            Function fun = FunctionResolver.get(keyspace, name, terms, receiver.ksName, receiver.cfName, receiver.type, UserFunctions.getCurrentUserFunctions(name, keyspace));
            if (fun == null)
                throw invalidRequest("Unknown function %s called", name);
            if (fun.isAggregate())
                throw invalidRequest("Aggregation function are not supported in the where clause");

            ScalarFunction scalarFun = (ScalarFunction) fun;

            // Functions.get() will complain if no function "name" type check with the provided arguments.
            // We still have to validate that the return type matches however
            if (!scalarFun.testAssignment(keyspace, receiver).isAssignable())
            {
                if (OperationFcts.isOperation(name))
                    throw invalidRequest("Type error: cannot assign result of operation %s (type %s) to %s (type %s)",
                                         OperationFcts.getOperator(scalarFun.name()), scalarFun.returnType().asCQL3Type(),
                                         receiver.name, receiver.type.asCQL3Type());

                throw invalidRequest("Type error: cannot assign result of function %s (type %s) to %s (type %s)",
                                     scalarFun.name(), scalarFun.returnType().asCQL3Type(),
                                     receiver.name, receiver.type.asCQL3Type());
            }

            if (fun.argTypes().size() != terms.size())

View on GitHub (pinned to 88fd0f6a0e)

Solutions

Remove the aggregate from the WHERE clause; compute the aggregate in a separate query first, then bind the value
Use a scalar (non-aggregate) function instead
Do two queries in the application: one for the aggregate, one filtered with its literal result

Example fix

// before
SELECT * FROM t WHERE v > avg(v);
// after
SELECT avg(v) FROM t; -- then use the returned value: SELECT * FROM t WHERE v > <avg>;

Defensive patterns

Strategy: validation

Validate before calling

const AGGREGATES = ['sum','count','avg','min','max','total'];
if (AGGREGATES.includes(fname.toLowerCase())) throw new Error('aggregates not allowed in WHERE clause');

Try / catch

try { rs = session.execute(q); } catch (InvalidRequestException e) { if (e.getMessage().includes('Aggregation function')) { /* split into two queries */ } else throw e; }

Prevention

Never place aggregates in WHERE clause terms
Compute aggregates in a separate query and bind literal results

When it happens

Trigger: Using an aggregate function inside a WHERE clause or other non-select-list term position, e.g. `SELECT * FROM t WHERE k = sum(x)` or an aggregate in a function-composed term.

Common situations: Porting SQL habits where aggregates are allowed in expressions; attempting to compare a column against an aggregate result in a single statement.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of apache/cassandra@88fd0f6a0e (2026-09-10). Data as JSON: /api/errors/a56fc5a5194b656d. Report an issue: GitHub.