What causes "GROUP BY ordinal %d is out of range (1 to %d)" in prestodb/presto?

Fix the ordinal so it is between 1 and the number of SELECT items.

prestodb/presto · error · SemanticException

NOT_SUPPORTED

NOT_SUPPORTED

Error message

GROUP BY ordinal %d is out of range (1 to %d)

What it means

During materialized-view-based query rewriting, MaterializedViewQueryOptimizer resolves GROUP BY ordinal references (e.g. GROUP BY 1) to the corresponding SELECT items. If the ordinal is less than 1 or greater than the number of select items, no matching expression exists and NOT_SUPPORTED is thrown. This protects the rewrite from producing a semantically invalid rewritten plan.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewQueryOptimizer.java:532

            }
            Table baseTable = (Table) relation;
            if (!removablePrefix.isPresent()) {
                removablePrefix = Optional.of(new Identifier(baseTable.getName().toString()));
            }
            if (node.getGroupBy().isPresent()) {
                List<SelectItem> selectItems = node.getSelect().getSelectItems();
                ImmutableSet.Builder<Expression> expressionsInGroupByBuilder = ImmutableSet.builder();
                for (GroupingElement element : node.getGroupBy().get().getGroupingElements()) {
                    element = removeGroupingElementPrefix(element, removablePrefix);
                    Optional<Set<Expression>> groupByOfMaterializedView = materializedViewInfo.getGroupBy();
                    if (groupByOfMaterializedView.isPresent()) {
                        for (Expression expression : element.getExpressions()) {
                            // Resolve ordinal references (e.g. GROUP BY 1) to the corresponding SELECT expression
                            Expression resolved = expression;
                            if (expression instanceof LongLiteral) {
                                int ordinal = toIntExact(((LongLiteral) expression).getValue());
                                if (ordinal < 1 || ordinal > selectItems.size()) {
                                    throw new SemanticException(NOT_SUPPORTED, expression, "GROUP BY ordinal %d is out of range (1 to %d)", ordinal, selectItems.size());
                                }
                                SelectItem selectItem = selectItems.get(ordinal - 1);
                                if (selectItem instanceof SingleColumn) {
                                    resolved = removeExpressionPrefix(((SingleColumn) selectItem).getExpression(), removablePrefix);
                                }
                                else {
                                    throw new IllegalStateException("GROUP BY ordinal references non-single-column select item");
                                }
                            }
                            if (!expressionRewriter.isExpressionInMvGroupBy(resolved, groupByOfMaterializedView.get()) || !materializedViewInfo.getBaseToViewColumnMap().containsKey(resolved)) {
                                throw new IllegalStateException(format("Grouping element %s is not present in materialized view groupBy field", element));
                            }
                            // Store the resolved expression so visitSingleColumn can match against it
                            expressionsInGroupByBuilder.add(resolved);
                        }
                    }
                    else {
                        expressionsInGroupByBuilder.addAll(element.getExpressions());

View on GitHub (pinned to 55bb57d202)

Solutions

Fix the ordinal so it is between 1 and the number of SELECT items.
Replace the ordinal with the explicit column expression, e.g. GROUP BY a instead of GROUP BY 1.
Regenerate the query if it is produced by a tool, keeping SELECT list and GROUP BY in sync.

Example fix

// before
SELECT a, b FROM mv GROUP BY 3;
// after
SELECT a, b FROM mv GROUP BY 1, 2;

Defensive patterns

Strategy: validation

Validate before calling

// validate GROUP BY ordinals against the SELECT list size before executing
function checkGroupByOrdinals(selectItemCount, groupByTerms) {
  const bad = groupByTerms.filter(t => /^\d+$/.test(t.trim()))
    .map(Number).filter(n => n < 1 || n > selectItemCount);
  if (bad.length) throw new Error(`GROUP BY ordinal ${bad.join(",")} out of range (1 to ${selectItemCount})`);
}

Prevention

Prefer explicit expressions over ordinals in GROUP BY.
When generating SQL, derive GROUP BY ordinals from the same array that builds the SELECT list.
Recount ordinals after every edit to the SELECT list.

When it happens

Trigger: Querying a materialized view with a GROUP BY that uses an out-of-range ordinal, e.g. `SELECT a, b FROM mv GROUP BY 3` when only 2 columns are selected.

Common situations: Programmatically generated SQL where select-list and GROUP BY are built independently and drift out of sync; hand-written queries with miscounted ordinals after editing the SELECT list.

Understand the failure class

Background: Presto NOT_SUPPORTED error: what "not supported" means and how to fix it — this error's family across 3 libraries.

Related errors

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