{"record":{"id":"eed149021c7c1555","repo":"bytebase/bytebase","slug":"the-number-of-columns-in-the-cte-q-returns-d-fie","errorCode":null,"errorMessage":"the number of columns in the CTE %q returns %d fields, but the column list returns %d fields","messagePattern":"the number of columns in the CTE %q returns (.+?) fields, but the column list returns (.+?) fields","errorType":"exception","errorClass":null,"httpStatus":null,"severity":"error","filePath":"backend/plugin/parser/snowflake/query_span_extractor.go","lineNumber":356,"sourceCode":"func applyCTEColumnList(cteName string, pseudoTable *base.PseudoTable, columns []ast.Ident) (*base.PseudoTable, error) {\n\tif pseudoTable == nil {\n\t\tpseudoTable = &base.PseudoTable{\n\t\t\tName:    cteName,\n\t\t\tColumns: []base.QuerySpanResult{},\n\t\t}\n\t}\n\n\tresult := &base.PseudoTable{\n\t\tName:    cteName,\n\t\tColumns: make([]base.QuerySpanResult, len(pseudoTable.GetQuerySpanResult())),\n\t}\n\tcopy(result.Columns, pseudoTable.GetQuerySpanResult())\n\n\tif len(columns) == 0 {\n\t\treturn result, nil\n\t}\n\tif len(columns) != len(result.GetQuerySpanResult()) {\n\t\treturn nil, errors.Errorf(\"the number of columns in the CTE %q returns %d fields, but the column list returns %d fields\", cteName, len(result.GetQuerySpanResult()), len(columns))\n\t}\n\tfor i, columnName := range columns {\n\t\tresult.Columns[i].Name = normalizeSnowflakeIdentifier(columnName)\n\t}\n\treturn result, nil\n}\n\nfunc mergeQuerySpanResults(currentColumns, newColumns []base.QuerySpanResult, cteName string) ([]base.QuerySpanResult, bool, error) {\n\tif len(currentColumns) != len(newColumns) {\n\t\treturn nil, false, errors.Errorf(\"recursive clause returns %d fields, but anchor clause returns %d fields in recursive CTE %q\", len(newColumns), len(currentColumns), cteName)\n\t}\n\n\tmergedColumns := make([]base.QuerySpanResult, len(currentColumns))\n\tcopy(mergedColumns, currentColumns)\n\tchanged := false\n\tfor i := range mergedColumns {\n\t\tvar hasChange bool\n\t\tmergedColumns[i].SourceColumns, hasChange = base.MergeSourceColumnSet(mergedColumns[i].SourceColumns, newColumns[i].SourceColumns)","sourceCodeStart":338,"sourceCodeEnd":374,"githubUrl":"https://github.com/bytebase/bytebase/blob/1870550677fe08f0d2a78c07acd27541464eb945/backend/plugin/parser/snowflake/query_span_extractor.go#L338-L374","documentation":"applyCTEColumnList validates that an explicit CTE column-name list has exactly as many names as the CTE body projects fields. When the counts differ it returns this error naming the CTE, the number of fields the body produced, and the number of names in the column list. It is raised for both plain and recursive CTEs (callers: extractCTEFromWith and extractRecursiveCTE).","triggerScenarios":"A WITH clause like WITH c(a,b,c) AS (SELECT 1, 2) where the body's SELECT list length (2) != the declared name count (3); also triggered on the recursive branch of a recursive CTE during fixed-point iteration.","commonSituations":"Typos/edit drift when a column is added to or removed from the CTE body but not the name list; code generation emitting alias lists from stale schemas; analyzing Snowflake SQL where a SELECT * in the CTE body expands to a different column count than expected.","solutions":["Make the number of identifiers in the WITH cte(...) list equal the number of columns in the CTE's SELECT list.","Remove the explicit column list and rely on the body's column names/aliases.","If the body uses SELECT *, expand it or pin an explicit projection so the arity is stable."],"exampleFix":"-- before\nWITH monthly(m, d) AS (SELECT month, day, revenue FROM sales)\nSELECT * FROM monthly;\n-- after\nWITH monthly(m, d, r) AS (SELECT month, day, revenue FROM sales)\nSELECT * FROM monthly;","handlingStrategy":"validation","validationCode":"-- SQL-side check before running analysis: count projections\n-- WITH c(a,b,c) AS (SELECT 1, 2) is invalid: 2 fields vs 3 names\nSELECT count(*) FROM (SELECT 1, 2) t; -- must equal len(column list)","typeGuard":null,"tryCatchPattern":"span, err := extractor.ExtractQuerySpan(ctx, query)\nif err != nil && strings.Contains(err.Error(), `but the column list returns`) {\n    return nil, fmt.Errorf(`fix WITH clause column list: %w`, err)\n}","preventionTips":["Count SELECT-list columns against the WITH name list whenever editing a CTE","Avoid SELECT * inside CTE bodies with declared column lists","Alias columns in the body and skip the name list entirely"],"tags":["snowflake","sql-analysis","cte","column-count-mismatch"],"backgroundTag":"shape-mismatch","analyzedSha":"1870550677fe08f0d2a78c07acd27541464eb945","analyzedAt":"2026-09-06T21:16:13.665Z","contentChangedAt":"2026-09-06T21:16:13.665Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}