{"record":{"id":"a56fc5a5194b656d","repo":"apache/cassandra","slug":"aggregation-function-are-not-supported-in-the-wher","errorCode":null,"errorMessage":"Aggregation function are not supported in the where clause","messagePattern":"Aggregation function are not supported in the where clause","errorType":"validation","errorClass":"InvalidRequestException","httpStatus":null,"severity":"error","filePath":"src/java/org/apache/cassandra/cql3/functions/FunctionCall.java","lineNumber":164,"sourceCode":"        public static Raw newNegation(Term.Raw raw)\n        {\n            FunctionName name = FunctionName.nativeFunction(OperationFcts.NEGATION_FUNCTION_NAME);\n            return new Raw(name, Collections.singletonList(raw));\n        }\n\n        public static Raw newCast(Term.Raw raw, CQL3Type type)\n        {\n            FunctionName name = FunctionName.nativeFunction(CastFcts.getFunctionName(type));\n            return new Raw(name, Collections.singletonList(raw));\n        }\n\n        public Term prepare(String keyspace, ColumnSpecification receiver) throws InvalidRequestException\n        {\n            Function fun = FunctionResolver.get(keyspace, name, terms, receiver.ksName, receiver.cfName, receiver.type, UserFunctions.getCurrentUserFunctions(name, keyspace));\n            if (fun == null)\n                throw invalidRequest(\"Unknown function %s called\", name);\n            if (fun.isAggregate())\n                throw invalidRequest(\"Aggregation function are not supported in the where clause\");\n\n            ScalarFunction scalarFun = (ScalarFunction) fun;\n\n            // Functions.get() will complain if no function \"name\" type check with the provided arguments.\n            // We still have to validate that the return type matches however\n            if (!scalarFun.testAssignment(keyspace, receiver).isAssignable())\n            {\n                if (OperationFcts.isOperation(name))\n                    throw invalidRequest(\"Type error: cannot assign result of operation %s (type %s) to %s (type %s)\",\n                                         OperationFcts.getOperator(scalarFun.name()), scalarFun.returnType().asCQL3Type(),\n                                         receiver.name, receiver.type.asCQL3Type());\n\n                throw invalidRequest(\"Type error: cannot assign result of function %s (type %s) to %s (type %s)\",\n                                     scalarFun.name(), scalarFun.returnType().asCQL3Type(),\n                                     receiver.name, receiver.type.asCQL3Type());\n            }\n\n            if (fun.argTypes().size() != terms.size())","sourceCodeStart":146,"sourceCodeEnd":182,"githubUrl":"https://github.com/apache/cassandra/blob/88fd0f6a0eaed8943f05ac9e8f947882b8ddc8f1/src/java/org/apache/cassandra/cql3/functions/FunctionCall.java#L146-L182","documentation":"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.","triggerScenarios":"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.","commonSituations":"Porting SQL habits where aggregates are allowed in expressions; attempting to compare a column against an aggregate result in a single statement.","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"],"exampleFix":"// before\nSELECT * FROM t WHERE v > avg(v);\n// after\nSELECT avg(v) FROM t; -- then use the returned value: SELECT * FROM t WHERE v > <avg>;","handlingStrategy":"validation","validationCode":"const AGGREGATES = ['sum','count','avg','min','max','total'];\nif (AGGREGATES.includes(fname.toLowerCase())) throw new Error('aggregates not allowed in WHERE clause');","typeGuard":null,"tryCatchPattern":"try { rs = session.execute(q); } catch (InvalidRequestException e) { if (e.getMessage().includes('Aggregation function')) { /* split into two queries */ } else throw e; }","preventionTips":["Never place aggregates in WHERE clause terms","Compute aggregates in a separate query and bind literal results"],"tags":["cql","functions","aggregates","where-clause"],"backgroundTag":"unsupported-operation","analyzedSha":"88fd0f6a0eaed8943f05ac9e8f947882b8ddc8f1","analyzedAt":"2026-09-10T07:29:22.284Z","contentChangedAt":"2026-09-10T07:29:22.284Z","schemaVersion":2},"datasetVersion":"2026-09-14T16:17:12.679Z"}