{"record":{"id":"d030b9b5e508dce8","repo":"prestodb/presto","slug":"must-be-column-reference","errorCode":"MUST_BE_COLUMN_REFERENCE","errorMessage":"GROUP BY expression must be a column reference: %s","messagePattern":"GROUP BY expression must be a column reference: (.+?)","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java","lineNumber":4408,"sourceCode":"                            }\n\n                            if (analysis.getColumnReferenceFields().containsKey(NodeRef.of(column))) {\n                                sets.add(ImmutableList.of(ImmutableSet.copyOf(analysis.getColumnReferenceFields().get(NodeRef.of(column)))));\n                            }\n                            else {\n                                verifyNoAggregateWindowOrGroupingFunctions(analysis.getFunctionHandles(), functionAndTypeResolver, column, \"GROUP BY clause\");\n                                analysis.recordSubqueries(node, analyzeExpression(column, scope));\n                                complexExpressions.add(column);\n                            }\n\n                            groupingExpressions.add(column);\n                        }\n                    }\n                    else {\n                        for (Expression column : groupingElement.getExpressions()) {\n                            analyzeExpression(column, scope);\n                            if (!analysis.getColumnReferences().contains(NodeRef.of(column))) {\n                                throw new SemanticException(SemanticErrorCode.MUST_BE_COLUMN_REFERENCE, column, \"GROUP BY expression must be a column reference: %s\", column);\n                            }\n\n                            groupingExpressions.add(column);\n                        }\n\n                        if (groupingElement instanceof Cube) {\n                            Set<FieldId> cube = groupingElement.getExpressions().stream()\n                                    .map(NodeRef::of)\n                                    .map(analysis.getColumnReferenceFields()::get)\n                                    .flatMap(Collection::stream)\n                                    .collect(toImmutableSet());\n\n                            cubes.add(cube);\n                        }\n                        else if (groupingElement instanceof Rollup) {\n                            List<FieldId> rollup = groupingElement.getExpressions().stream()\n                                    .map(NodeRef::of)\n                                    .map(analysis.getColumnReferenceFields()::get)","sourceCodeStart":4390,"sourceCodeEnd":4426,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java#L4390-L4426","documentation":"For non-simple grouping elements, every GROUP BY expression must resolve to a column reference present in the analysis's collected column references. If the analyzed expression is an arbitrary (non-column) expression that isn't registered as a column reference, Presto rejects it with MUST_BE_COLUMN_REFERENCE. GROUP BY items in these positions must be plain column references, not computed expressions.","triggerScenarios":"GROUP BY containing arbitrary expressions such as arithmetic (a + b), function calls (lower(name)) or CASE expressions in contexts requiring column references, where the expression isn't a bare column reference resolved in the scope.","commonSituations":"Users porting MySQL/Hive habits of grouping by expressions; queries like GROUP BY date_trunc('day', ts) in a position where only column refs are allowed; aliases from SELECT used directly in GROUP BY with expressions.","solutions":["Group by the underlying columns instead of the expression (e.g. GROUP BY a, b instead of GROUP BY a + b).","If expression grouping is needed, use GROUP BY with the expression via a subquery that projects it as a column, then group by that column.","Use the select-list alias with a matching simple column projection so it resolves to a column reference."],"exampleFix":"// before\nSELECT a + b, count(*) FROM t GROUP BY a + b;\n// after\nSELECT a, b, count(*) FROM t GROUP BY a, b;","handlingStrategy":"validation","validationCode":"for (Expression g : groupByExpressions) {\n    if (!(g instanceof Identifier || g instanceof DereferenceExpression || g instanceof LongLiteral)) {\n        throw new IllegalArgumentException(\"GROUP BY expression must be a column reference: \" + g);\n    }\n}","typeGuard":"boolean isColumnRef(Expression e) {\n    return e instanceof Identifier || e instanceof DereferenceExpression;\n}","tryCatchPattern":"try { execute(sql); } catch (SemanticException e) { if (e.getCode() == MUST_BE_COLUMN_REFERENCE) { /* rewrite to group by underlying columns or pre-project in a subquery */ } else { throw e; } }","preventionTips":["Group by base columns, not computed expressions.","Pre-project expressions as columns in a subquery, then group by the new column.","Avoid copying expression-GROUP BY idioms from other engines without checking."],"tags":["sql","presto","group-by","column-reference"],"backgroundTag":"group-by-expression-not-allowed","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"}