{"record":{"id":"8ea91f6ccb353f72","repo":"bytebase/bytebase","slug":"cte-table-expr-has-d-columns-but-alias-has-d-co","errorCode":null,"errorMessage":"cte table expr has %d columns, but alias has %d columns","messagePattern":"cte table expr has (.+?) columns, but alias has (.+?) columns","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/plugin/parser/tidb/query_span_extractor.go","lineNumber":298,"sourceCode":"\t\t\tcolumns = append(columns, base.QuerySpanResult{\n\t\t\t\tName:          node.Cols[i].L,\n\t\t\t\tSourceColumns: item.SourceColumns,\n\t\t\t})\n\t\t}\n\t\treturn base.NewPseudoTable(node.ViewName.Name.O, columns), nil\n\t}\n\treturn nil, nil\n}\n\nfunc (q *querySpanExtractor) extractNonRecursiveCTE(node *tidbast.CommonTableExpression) (*base.PseudoTable, error) {\n\ttableSource, err := q.extractTableSourceFromNode(node.Query.Query)\n\tif err != nil {\n\t\treturn nil, errors.Wrap(err, \"failed to extract table source from CTE query\")\n\t}\n\tquerySpanResults := tableSource.GetQuerySpanResult()\n\tif len(node.ColNameList) > 0 {\n\t\tif len(node.ColNameList) != len(querySpanResults) {\n\t\t\treturn nil, errors.Errorf(\"cte table expr has %d columns, but alias has %d columns\", len(querySpanResults), len(node.ColNameList))\n\t\t}\n\t\tfor i, name := range node.ColNameList {\n\t\t\t// The column name for MySQL is case insensitive.\n\t\t\tquerySpanResults[i].Name = name.L\n\t\t}\n\t}\n\n\treturn &base.PseudoTable{\n\t\tName:    node.Name.O,\n\t\tColumns: querySpanResults,\n\t}, nil\n}\n\nfunc (q *querySpanExtractor) extractRecursiveCTE(node *tidbast.CommonTableExpression) (*base.PseudoTable, error) {\n\tswitch x := node.Query.Query.(type) {\n\tcase *tidbast.SetOprStmt:\n\t\tif x.With != nil {\n\t\t\tpreviousCteOuterLength := len(q.ctes)","sourceCodeStart":280,"sourceCodeEnd":316,"githubUrl":"https://github.com/bytebase/bytebase/blob/1870550677fe08f0d2a78c07acd27541464eb945/backend/plugin/parser/tidb/query_span_extractor.go#L280-L316","documentation":"After extracting a non-recursive CTE's columns, extractNonRecursiveCTE compares the column count of the CTE body's result with the length of its alias column list (ColNameList, the names after the CTE name in WITH cte (a, b) AS ...). A mismatch is invalid SQL and aborts with this error reporting both counts.","triggerScenarios":"WITH cte (alias1, alias2) AS (SELECT col1) ... where len(ColNameList) != number of columns the CTE's SELECT returns.","commonSituations":"Hand-written CTEs where the alias list was edited without updating the inner SELECT; code-generated SQL emitting fixed alias lists; schema changes shrinking the inner SELECT's columns.","solutions":["Make the alias column list count match the CTE's SELECT column count (add/remove aliases)","Run the CTE's inner SELECT and count its columns, then adjust the WITH clause","Remove the alias list entirely to let the CTE inherit the SELECT's column names"],"exampleFix":"// before\nWITH cte (a, b, c) AS (SELECT x, y FROM t) SELECT * FROM cte;\n// after\nWITH cte (a, b) AS (SELECT x, y FROM t) SELECT * FROM cte;","handlingStrategy":"validation","validationCode":"// count columns of the CTE inner SELECT and compare to the alias list before use\ninnerCols := countSelectColumns(cteInnerSQL) // via parser\nif len(cteAliasNames) > 0 && len(cteAliasNames) != innerCols {\n\treturn fmt.Errorf(\"CTE alias list has %d names but SELECT returns %d columns\", len(cteAliasNames), innerCols)\n}","typeGuard":null,"tryCatchPattern":"span, err := GetQuerySpan(ctx, gCtx, stmt, db, \"\", false)\nif err != nil {\n\tif strings.Contains(err.Error(), \"columns, but alias has\") {\n\t\treturn nil, fmt.Errorf(\"fix WITH clause alias count: %w\", err)\n\t}\n\treturn nil, err\n}","preventionTips":["Keep CTE alias lists in sync when editing the inner SELECT","Omit the alias list to inherit the SELECT's column names","Parse the inner SELECT and count output columns in generated SQL"],"tags":["tidb","cte","column-count"],"backgroundTag":"shape-mismatch","analyzedSha":"1870550677fe08f0d2a78c07acd27541464eb945","analyzedAt":"2026-09-06T21:16:13.665Z","contentChangedAt":"2026-09-06T21:16:13.665Z","schemaVersion":2},"datasetVersion":"2026-09-14T05:17:10.506Z"}