Nested CTEs - Parser returns CTE as a table when there is nested CTE

[arunbalasubramani]

When there are nested CTEs, the parser returns invalid tables. It returns one of the CTEs as a table.

SQL:

WITH CTE_ROOT_1 as (
              With CTE_CHILD as (
                  SELECT a
                  FROM table_1 as t
              )
              SELECT a
              FROM CTE_CHILD 
          )

    , CTE_ROOT_2 as (
      SELECT b
      FROM table_2
      )

    SELECT a,b,c
    FROM table_3 t3
    LEFT JOIN CTE_ROOT_1 cr1 on t3.id = cr1.id
    LEFT JOIN CTE_ROOT_2 cr2 on t3.id = cr2.id
    LEFT JOIN table_4 t4 on t3.id = t4.id

Expected:

['table_1', 'table_2', 'table_3', 'table_4']

Actual:

['table_1', 'CTE_ROOT_2', 'table_2', 'table_3', 'table_4']

[cuong-pham]

Is that a common way to write CTE?

I generally see people write like this

With CTE_CHILD as (
  SELECT a
  FROM table_1 as t
),
CTE_ROOT_1 as (
  SELECT a
    FROM CTE_CHILD
), CTE_ROOT_2 as (
  SELECT b
    FROM table_2
)

SELECT a,b,c
FROM table_3 t3
LEFT JOIN CTE_ROOT_1 cr1 on t3.id = cr1.id
LEFT JOIN CTE_ROOT_2 cr2 on t3.id = cr2.id
LEFT JOIN table_4 t4 on t3.id = t4.id