bytebase/bytebase · error

cte table expr has %d columns, but alias has %d columns

Error message

cte table expr has %d columns, but alias has %d columns

What it means

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.

Source

Thrown at backend/plugin/parser/tidb/query_span_extractor.go:298

			columns = append(columns, base.QuerySpanResult{
				Name:          node.Cols[i].L,
				SourceColumns: item.SourceColumns,
			})
		}
		return base.NewPseudoTable(node.ViewName.Name.O, columns), nil
	}
	return nil, nil
}

func (q *querySpanExtractor) extractNonRecursiveCTE(node *tidbast.CommonTableExpression) (*base.PseudoTable, error) {
	tableSource, err := q.extractTableSourceFromNode(node.Query.Query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to extract table source from CTE query")
	}
	querySpanResults := tableSource.GetQuerySpanResult()
	if len(node.ColNameList) > 0 {
		if len(node.ColNameList) != len(querySpanResults) {
			return nil, errors.Errorf("cte table expr has %d columns, but alias has %d columns", len(querySpanResults), len(node.ColNameList))
		}
		for i, name := range node.ColNameList {
			// The column name for MySQL is case insensitive.
			querySpanResults[i].Name = name.L
		}
	}

	return &base.PseudoTable{
		Name:    node.Name.O,
		Columns: querySpanResults,
	}, nil
}

func (q *querySpanExtractor) extractRecursiveCTE(node *tidbast.CommonTableExpression) (*base.PseudoTable, error) {
	switch x := node.Query.Query.(type) {
	case *tidbast.SetOprStmt:
		if x.With != nil {
			previousCteOuterLength := len(q.ctes)

View on GitHub (pinned to 1870550677)

Solutions

1. Make the alias column list count match the CTE's SELECT column count (add/remove aliases)
2. Run the CTE's inner SELECT and count its columns, then adjust the WITH clause
3. Remove the alias list entirely to let the CTE inherit the SELECT's column names

Example fix

// before
WITH cte (a, b, c) AS (SELECT x, y FROM t) SELECT * FROM cte;
// after
WITH cte (a, b) AS (SELECT x, y FROM t) SELECT * FROM cte;

// count columns of the CTE inner SELECT and compare to the alias list before use
innerCols := countSelectColumns(cteInnerSQL) // via parser
if len(cteAliasNames) > 0 && len(cteAliasNames) != innerCols {
	return fmt.Errorf("CTE alias list has %d names but SELECT returns %d columns", len(cteAliasNames), innerCols)
}

span, err := GetQuerySpan(ctx, gCtx, stmt, db, "", false)
if err != nil {
	if strings.Contains(err.Error(), "columns, but alias has") {
		return nil, fmt.Errorf("fix WITH clause alias count: %w", err)
	}
	return nil, err
}

When it happens

Trigger: WITH cte (alias1, alias2) AS (SELECT col1) ... where len(ColNameList) != number of columns the CTE's SELECT returns.

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

Related errors

• MySQL CTE column list should have the same length, but got %
• prior backup does not support WITH (CTE) statements when mor
• The used SELECT statements have a different number of column
• failed to extract table source from CTE query
• Failed to find initial part for recursive common table expre

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