SQLite: WithRecursive generates invalid SQL with extra parentheses in UNION ALL

[ilxqx]

Description

When using WithRecursive() with UnionAll() in SQLite, Bun generates invalid SQL that wraps each SELECT statement in the UNION with parentheses, which causes a syntax error in SQLite.

Expected SQL (correct)

WITH RECURSIVE "category_tree" AS (
  SELECT ... 
  UNION ALL 
  SELECT ...
)

Actual SQL (incorrect)

WITH RECURSIVE "category_tree" AS (
  (SELECT ...) 
  UNION ALL 
  (SELECT ...)
)

SQLite does not support parentheses around individual SELECT statements within a UNION inside a CTE.

Reproduction

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/sqlitedialect"
	"github.com/uptrace/bun/driver/sqliteshim"
)

type Category struct {
	bun.BaseModel `bun:"test_category,alias:c"`
	ID            int64  `bun:"id,pk,autoincrement"`
	Name          string `bun:"name,notnull"`
	ParentID      *int64 `bun:"parent_id"`
}

func main() {
	ctx := context.Background()
	sqldb, _ := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
	defer sqldb.Close()

	db := bun.NewDB(sqldb, sqlitedialect.New())
	
	_, _ = db.NewCreateTable().Model((*Category)(nil)).Exec(ctx)
	
	var results []Category
	err := db.NewSelect().
		WithRecursive("category_tree", db.NewSelect().
			Model((*Category)(nil)).
			Where("name LIKE ?", "%test%").
			UnionAll(
				db.NewSelect().
					Model((*Category)(nil)).
					Join("JOIN ? AS ?", bun.Ident("category_tree"), bun.Ident("ct")).
					JoinOn("?TableAlias.? = ?", bun.Ident("id"), bun.Ident("ct.parent_id")),
			),
		).
		TableExpr("?", bun.Ident("category_tree")).
		Scan(ctx, &results)

	if err != nil {
		log.Fatal(err) // SQL logic error: near "(": syntax error
	}
}

Error

SQL logic error: near "(": syntax error (1)

SQLite Verification

# With parentheses (fails)
$ sqlite3 :memory: "WITH RECURSIVE t AS ((SELECT 1) UNION ALL (SELECT 2)) SELECT * FROM t"
Error: near "(": syntax error

# Without parentheses (works)
$ sqlite3 :memory: "WITH RECURSIVE t AS (SELECT 1 UNION ALL SELECT 2) SELECT * FROM t"

Root Cause

In query_select.go lines 663-674, the code adds parentheses around each UNION part:

if len(q.union) > 0 {
    b = append(b, ')')
    
    for _, u := range q.union {
        b = append(b, u.expr...)
        b = append(b, '(')              // Problem: adds left parenthesis
        b, err = u.query.AppendQuery(gen, b)
        if err != nil {
            return nil, err
        }
        b = append(b, ')')              // Problem: adds right parenthesis
    }
}

Workaround

Use db.NewRaw() instead of WithRecursive():

err := db.NewRaw(`
    WITH RECURSIVE "category_tree" AS (
        SELECT ... UNION ALL SELECT ...
    ) SELECT * FROM "category_tree"
`).Scan(ctx, &results)

Impact

• Affects: SQLite only (PostgreSQL and other databases allow parentheses in UNION)
• Severity: Recursive CTEs with UNION are completely broken in SQLite

Possible Solution

The parentheses should be conditionally added based on:

1. Whether the query is inside a CTE (WITH clause)
2. The dialect being used (SQLite vs others)
   For SQLite specifically, when generating SQL inside a CTE definition, the UNION parts should not be wrapped in parentheses.