{"record":{"id":"86edec94d86c1a28","repo":"vitessio/vitess","slug":"group-by-expression-is-not-allowed-to-reference-an","errorCode":null,"errorMessage":"group by expression is not allowed to reference an aggregate expression: %v","messagePattern":"group by expression is not allowed to reference an aggregate expression: (.+?)","errorType":"validation","errorClass":null,"httpStatus":null,"severity":"error","filePath":"go/vt/vttablet/tabletmanager/vreplication/table_plan_builder.go","lineNumber":564,"sourceCode":"\t})\n}\n\nfunc (tpb *tablePlanBuilder) analyzeGroupBy(groupBy *sqlparser.GroupBy) error {\n\tif groupBy == nil {\n\t\t// If there's no grouping, the it's an insertNormal.\n\t\treturn nil\n\t}\n\tfor _, expr := range groupBy.Exprs {\n\t\tcolname, ok := expr.(*sqlparser.ColName)\n\t\tif !ok {\n\t\t\treturn fmt.Errorf(\"unsupported non-column name or alias in group by clause: %v\", sqlparser.String(expr))\n\t\t}\n\t\tcexpr := tpb.findCol(colname.Name)\n\t\tif cexpr == nil {\n\t\t\treturn fmt.Errorf(\"group by expression does not reference an alias in the select list: %v\", sqlparser.String(expr))\n\t\t}\n\t\tif cexpr.operation != opExpr {\n\t\t\treturn fmt.Errorf(\"group by expression is not allowed to reference an aggregate expression: %v\", sqlparser.String(expr))\n\t\t}\n\t\tcexpr.isGrouped = true\n\t}\n\t// If all colExprs are grouped, then it's an insertIgnore.\n\ttpb.onInsert = insertIgnore\n\tfor _, cExpr := range tpb.colExprs {\n\t\tif !cExpr.isGrouped {\n\t\t\t// If some colExprs are not grouped, then it's an insertOnDup.\n\t\t\ttpb.onInsert = insertOnDup\n\t\t\tbreak\n\t\t}\n\t}\n\treturn nil\n}\n\nfunc (tpb *tablePlanBuilder) getPKColsInfo(uniqueKeyColumns []string, colInfos []*ColumnInfo) (pkColsInfo []*ColumnInfo) {\n\tif len(uniqueKeyColumns) == 0 {\n\t\t// No PK override","sourceCodeStart":546,"sourceCodeEnd":582,"githubUrl":"https://github.com/vitessio/vitess/blob/01a25a7d176f94613b8d59d799f438380a8760e4/go/vt/vttablet/tabletmanager/vreplication/table_plan_builder.go#L546-L582","documentation":"A GROUP BY column must reference a plain (non-aggregate) select-list expression. If the group-by alias resolves to a count/sum item (operation != opExpr), the builder rejects it because grouping by an aggregate is meaningless for the materializer's insertIgnore plan.","triggerScenarios":"Materialize/vreplication query like SELECT count(*) AS c FROM t GROUP BY c; analyzeGroupBy finds cexpr with operation opCount/opSum instead of opExpr.","commonSituations":"Users trying to group by an aggregate output; hand-written queries mixing grouping levels that MySQL itself would reject outside derived tables.","solutions":["Remove the aggregate from the GROUP BY clause; only group by base columns selected in the list.","Group by the underlying column and aggregate separately: SELECT k, count(*) AS c FROM t GROUP BY k.","If aggregate-of-aggregates is needed, materialize first and run a second query over the materialized table."],"exampleFix":"// before\nselect count(*) as c from t group by c\n// after\nselect k, count(*) as c from t group by k","handlingStrategy":"validation","validationCode":"// Require GROUP BY items to reference non-aggregate select expressions\nfunc groupByRefsPlainCols(sel *sqlparser.Select) bool {\n    for _, e := range sel.GroupBy.Exprs {\n        c, ok := e.(*sqlparser.ColName)\n        if !ok { return false }\n        for _, se := range sel.SelectExprs {\n            ae, ok := se.(*sqlparser.AliasedExpr)\n            if !ok { continue }\n            if ae.As.String() == c.Name.String() {\n                switch ae.Expr.(type) {\n                case *sqlparser.CountStar, *sqlparser.Sum:\n                    return false\n                }\n            }\n        }\n    }\n    return true\n}","typeGuard":"func isAggregateExpr(n sqlparser.SQLNode) bool { switch n.(type) { case *sqlparser.CountStar, *sqlparser.Sum: return true }; return false }","tryCatchPattern":null,"preventionTips":["Only group by base columns, never by aggregate outputs.","Use the canonical pattern: SELECT key, count(*) FROM t GROUP BY key.","Do aggregate-of-aggregate analysis in a second query over the materialized table."],"tags":["vreplication","sql","group-by","aggregation","vttablet"],"backgroundTag":"unsupported-group-by-expression","analyzedSha":"01a25a7d176f94613b8d59d799f438380a8760e4","analyzedAt":"2026-09-01T17:28:30.605Z","contentChangedAt":null,"schemaVersion":2},"datasetVersion":"2026-09-08T10:18:20.063Z"}