What causes "GROUP BY expression must be a column reference: %s" in prestodb/presto?

Group by the underlying columns instead of the expression (e.g. GROUP BY a, b instead of GROUP BY a + b).

prestodb/presto · error · SemanticException

MUST_BE_COLUMN_REFERENCE

MUST_BE_COLUMN_REFERENCE

Error message

GROUP BY expression must be a column reference: %s

What it means

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.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java:4408

                            }

                            if (analysis.getColumnReferenceFields().containsKey(NodeRef.of(column))) {
                                sets.add(ImmutableList.of(ImmutableSet.copyOf(analysis.getColumnReferenceFields().get(NodeRef.of(column)))));
                            }
                            else {
                                verifyNoAggregateWindowOrGroupingFunctions(analysis.getFunctionHandles(), functionAndTypeResolver, column, "GROUP BY clause");
                                analysis.recordSubqueries(node, analyzeExpression(column, scope));
                                complexExpressions.add(column);
                            }

                            groupingExpressions.add(column);
                        }
                    }
                    else {
                        for (Expression column : groupingElement.getExpressions()) {
                            analyzeExpression(column, scope);
                            if (!analysis.getColumnReferences().contains(NodeRef.of(column))) {
                                throw new SemanticException(SemanticErrorCode.MUST_BE_COLUMN_REFERENCE, column, "GROUP BY expression must be a column reference: %s", column);
                            }

                            groupingExpressions.add(column);
                        }

                        if (groupingElement instanceof Cube) {
                            Set<FieldId> cube = groupingElement.getExpressions().stream()
                                    .map(NodeRef::of)
                                    .map(analysis.getColumnReferenceFields()::get)
                                    .flatMap(Collection::stream)
                                    .collect(toImmutableSet());

                            cubes.add(cube);
                        }
                        else if (groupingElement instanceof Rollup) {
                            List<FieldId> rollup = groupingElement.getExpressions().stream()
                                    .map(NodeRef::of)
                                    .map(analysis.getColumnReferenceFields()::get)

View on GitHub (pinned to 55bb57d202)

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.

Example fix

// before
SELECT a + b, count(*) FROM t GROUP BY a + b;
// after
SELECT a, b, count(*) FROM t GROUP BY a, b;

Defensive patterns

Strategy: validation

Validate before calling

for (Expression g : groupByExpressions) {
    if (!(g instanceof Identifier || g instanceof DereferenceExpression || g instanceof LongLiteral)) {
        throw new IllegalArgumentException("GROUP BY expression must be a column reference: " + g);
    }
}

Type guard

boolean isColumnRef(Expression e) {
    return e instanceof Identifier || e instanceof DereferenceExpression;
}

Try / catch

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

Prevention

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.

When it happens

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

Common situations: 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.

Related errors

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