{"record":{"id":"86670a1b427d98b2","repo":"prestodb/presto","slug":"not-supported-86670a","errorCode":"NOT_SUPPORTED","errorMessage":"GROUP BY ordinal %d is out of range (1 to %d)","messagePattern":"GROUP BY ordinal (.+?) is out of range \\(1 to (.+?)\\)","errorType":"error_code","errorClass":"SemanticException","httpStatus":null,"severity":"error","filePath":"presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewQueryOptimizer.java","lineNumber":532,"sourceCode":"            }\n            Table baseTable = (Table) relation;\n            if (!removablePrefix.isPresent()) {\n                removablePrefix = Optional.of(new Identifier(baseTable.getName().toString()));\n            }\n            if (node.getGroupBy().isPresent()) {\n                List<SelectItem> selectItems = node.getSelect().getSelectItems();\n                ImmutableSet.Builder<Expression> expressionsInGroupByBuilder = ImmutableSet.builder();\n                for (GroupingElement element : node.getGroupBy().get().getGroupingElements()) {\n                    element = removeGroupingElementPrefix(element, removablePrefix);\n                    Optional<Set<Expression>> groupByOfMaterializedView = materializedViewInfo.getGroupBy();\n                    if (groupByOfMaterializedView.isPresent()) {\n                        for (Expression expression : element.getExpressions()) {\n                            // Resolve ordinal references (e.g. GROUP BY 1) to the corresponding SELECT expression\n                            Expression resolved = expression;\n                            if (expression instanceof LongLiteral) {\n                                int ordinal = toIntExact(((LongLiteral) expression).getValue());\n                                if (ordinal < 1 || ordinal > selectItems.size()) {\n                                    throw new SemanticException(NOT_SUPPORTED, expression, \"GROUP BY ordinal %d is out of range (1 to %d)\", ordinal, selectItems.size());\n                                }\n                                SelectItem selectItem = selectItems.get(ordinal - 1);\n                                if (selectItem instanceof SingleColumn) {\n                                    resolved = removeExpressionPrefix(((SingleColumn) selectItem).getExpression(), removablePrefix);\n                                }\n                                else {\n                                    throw new IllegalStateException(\"GROUP BY ordinal references non-single-column select item\");\n                                }\n                            }\n                            if (!expressionRewriter.isExpressionInMvGroupBy(resolved, groupByOfMaterializedView.get()) || !materializedViewInfo.getBaseToViewColumnMap().containsKey(resolved)) {\n                                throw new IllegalStateException(format(\"Grouping element %s is not present in materialized view groupBy field\", element));\n                            }\n                            // Store the resolved expression so visitSingleColumn can match against it\n                            expressionsInGroupByBuilder.add(resolved);\n                        }\n                    }\n                    else {\n                        expressionsInGroupByBuilder.addAll(element.getExpressions());","sourceCodeStart":514,"sourceCodeEnd":550,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-main-base/src/main/java/com/facebook/presto/sql/analyzer/MaterializedViewQueryOptimizer.java#L514-L550","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"// before\nSELECT a, b FROM mv GROUP BY 3;\n// after\nSELECT a, b FROM mv GROUP BY 1, 2;","handlingStrategy":"validation","validationCode":"// validate GROUP BY ordinals against the SELECT list size before executing\nfunction checkGroupByOrdinals(selectItemCount, groupByTerms) {\n  const bad = groupByTerms.filter(t => /^\\d+$/.test(t.trim()))\n    .map(Number).filter(n => n < 1 || n > selectItemCount);\n  if (bad.length) throw new Error(`GROUP BY ordinal ${bad.join(\",\")} out of range (1 to ${selectItemCount})`);\n}","typeGuard":null,"tryCatchPattern":null,"preventionTips":["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."],"tags":["materialized-view","group-by","ordinal","query-rewrite"],"backgroundTag":"group-by-ordinal-out-of-range","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"}