[BUG]: Unnecessary parentheses in UNION ALL subqueries for CTE statements causing syntax errors

[wmzy]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.40.1

What version of drizzle-kit are you using?

0.30.5

Other packages

No response

Describe the Bug

Description

When using the unionAll method to build Common Table Expressions (CTEs), Drizzle ORM adds extra parentheses around each subquery, which can cause syntax errors in some cases.

Steps to Reproduce

When creating a CTE using this pattern:

const cte = qb.$with('name').as(
  qb
    .select()
    .from(tableA)
    .where(/* condition */)
    .unionAll(
      qb
        .select()
        .from(tableB)
        .where(/* condition */)
    )
);

Generated SQL (with issue)

with "name" as (
    (select columns from tableA where condition) 
    union all 
    (select columns from tableB where condition)
)

Expected SQL

with "name" as (
    select columns from tableA where condition
    union all 
    select columns from tableB where condition
)

Root Cause

In the Drizzle ORM source code, the unionAll method implementation automatically adds parentheses around each subquery. While this might be necessary for standalone UNION ALL queries, it's redundant when building CTE subqueries and can lead to syntax errors.

Suggested Fix

Modify the unionAll method implementation to avoid adding extra parentheses in CTE contexts, or provide an option to control whether parentheses are added around subqueries.