bytebase/bytebase · error

the number of columns in the CTE %q returns %d fields, but t

Error message

the number of columns in the CTE %q returns %d fields, but the column list returns %d fields

What it means

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).

Source

Thrown at backend/plugin/parser/snowflake/query_span_extractor.go:356

func applyCTEColumnList(cteName string, pseudoTable *base.PseudoTable, columns []ast.Ident) (*base.PseudoTable, error) {
	if pseudoTable == nil {
		pseudoTable = &base.PseudoTable{
			Name:    cteName,
			Columns: []base.QuerySpanResult{},
		}
	}

	result := &base.PseudoTable{
		Name:    cteName,
		Columns: make([]base.QuerySpanResult, len(pseudoTable.GetQuerySpanResult())),
	}
	copy(result.Columns, pseudoTable.GetQuerySpanResult())

	if len(columns) == 0 {
		return result, nil
	}
	if len(columns) != len(result.GetQuerySpanResult()) {
		return 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))
	}
	for i, columnName := range columns {
		result.Columns[i].Name = normalizeSnowflakeIdentifier(columnName)
	}
	return result, nil
}

func mergeQuerySpanResults(currentColumns, newColumns []base.QuerySpanResult, cteName string) ([]base.QuerySpanResult, bool, error) {
	if len(currentColumns) != len(newColumns) {
		return nil, false, errors.Errorf("recursive clause returns %d fields, but anchor clause returns %d fields in recursive CTE %q", len(newColumns), len(currentColumns), cteName)
	}

	mergedColumns := make([]base.QuerySpanResult, len(currentColumns))
	copy(mergedColumns, currentColumns)
	changed := false
	for i := range mergedColumns {
		var hasChange bool
		mergedColumns[i].SourceColumns, hasChange = base.MergeSourceColumnSet(mergedColumns[i].SourceColumns, newColumns[i].SourceColumns)

View on GitHub (pinned to 1870550677)

Solutions

1. Make the number of identifiers in the WITH cte(...) list equal the number of columns in the CTE's SELECT list.
2. Remove the explicit column list and rely on the body's column names/aliases.
3. If the body uses SELECT *, expand it or pin an explicit projection so the arity is stable.

Example fix

-- before
WITH monthly(m, d) AS (SELECT month, day, revenue FROM sales)
SELECT * FROM monthly;
-- after
WITH monthly(m, d, r) AS (SELECT month, day, revenue FROM sales)
SELECT * FROM monthly;

-- SQL-side check before running analysis: count projections
-- WITH c(a,b,c) AS (SELECT 1, 2) is invalid: 2 fields vs 3 names
SELECT count(*) FROM (SELECT 1, 2) t; -- must equal len(column list)

span, err := extractor.ExtractQuerySpan(ctx, query)
if err != nil && strings.Contains(err.Error(), `but the column list returns`) {
    return nil, fmt.Errorf(`fix WITH clause column list: %w`, err)
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• recursive clause returns %d fields, but anchor clause return
• the number of columns in the left part of the set operation
• The common table expression and column names list have diffe
• failed to extract sensitive fields of the CTE %q
• failed to extract sensitive fields of the recursive clause o

AI-assisted analysis of bytebase/bytebase@1870550677 (2026-09-06). Data as JSON: /api/errors/eed149021c7c1555. Report an issue: GitHub.