prestodb/presto · error · SemanticException

TOO_MANY_GROUPING_SETS

TOO_MANY_GROUPING_SETS

Error message

GROUP BY has more than %s grouping sets but can contain at most %s

What it means

During GROUP BY analysis Presto computes the cross-product size of all grouping sets (CUBE/ROLLUP/GROUPING SETS elements). If the multiplication overflows a long (Math.multiplyExact throws ArithmeticException), the analyzer reports that the GROUP BY implies more than Integer.MAX_VALUE grouping sets, exceeding the configured maximum (session/system max grouping sets). The query is rejected before execution.

Source

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

                        int exponent = element.getExpressions().size();
                        if (exponent > 30) {
                            throw new ArithmeticException();
                        }
                        product = 1 << exponent;
                    }
                    else if (element instanceof Rollup) {
                        product = element.getExpressions().size() + 1;
                    }
                    else if (element instanceof GroupingSets) {
                        product = ((GroupingSets) element).getSets().size();
                    }
                    else {
                        throw new UnsupportedOperationException("Unsupported grouping element type: " + element.getClass().getName());
                    }
                    crossProduct = Math.multiplyExact(crossProduct, product);
                }
                catch (ArithmeticException e) {
                    throw new SemanticException(TOO_MANY_GROUPING_SETS, node,
                            "GROUP BY has more than %s grouping sets but can contain at most %s", Integer.MAX_VALUE, getMaxGroupingSets(session));
                }
                if (crossProduct > getMaxGroupingSets(session)) {
                    throw new SemanticException(TOO_MANY_GROUPING_SETS, node,
                            "GROUP BY has %s grouping sets but can contain at most %s", crossProduct, getMaxGroupingSets(session));
                }
            }
        }

        private List<Expression> analyzeGroupBy(QuerySpecification node, Scope scope, List<Expression> outputExpressions)
        {
            if (node.getGroupBy().isPresent()) {
                ImmutableList.Builder<Set<FieldId>> cubes = ImmutableList.builder();
                ImmutableList.Builder<List<FieldId>> rollups = ImmutableList.builder();
                ImmutableList.Builder<List<Set<FieldId>>> sets = ImmutableList.builder();
                ImmutableList.Builder<Expression> complexExpressions = ImmutableList.builder();
                ImmutableList.Builder<Expression> groupingExpressions = ImmutableList.builder();

View on GitHub (pinned to 55bb57d202)

Solutions

Reduce the number of columns in CUBE/GROUPING SETS so the total set count stays within the limit.
Replace full CUBE with explicit GROUPING SETS listing only the combinations actually needed.
Raise the grouping-sets limit via its session/config property if the query is legitimate and cluster resources allow.

Example fix

// before
SELECT a, b, c, d, e, f, g, h, count(*) FROM t GROUP BY CUBE (a,b,c,d,e,f,g,h);
// after
SELECT a, b, c, count(*) FROM t GROUP BY CUBE (a,b,c);

Defensive patterns

Strategy: validation

Validate before calling

long sets = 1;
for (GroupingElement e : groupByElements) {
    int product = e instanceof Cube ? (1 << e.getExpressions().size()) : (e instanceof Rollup ? e.getExpressions().size() + 1 : 1);
    sets = Math.multiplyExact(sets, product);
}
if (sets > maxGroupingSets) throw new IllegalArgumentException("Too many grouping sets: " + sets);

Try / catch

try { session.execute(sql); } catch (SemanticException e) { if (e.getCode() == TOO_MANY_GROUPING_SETS) { /* reduce CUBE columns or use explicit GROUPING SETS */ } else { throw e; } }

Prevention

Compute 2^n for CUBE columns before emitting the query.
Prefer explicit GROUPING SETS over full CUBE in generated BI SQL.
Know the cluster's max grouping sets config and enforce it in your query builder.

When it happens

Trigger: A GROUP BY with many CUBE or GROUPING SETS columns whose combined product of per-element set counts overflows (e.g. CUBE on 40+ columns yields 2^40 sets), or any product above the configured max grouping sets limit.

Common situations: Autogenerated BI queries that CUBE every dimension column; users unaware grouping sets grow exponentially with cube columns; queries migrated from engines without such limits.

Related errors

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