Selecting over a with clause that contains union all

Question

Assume that you have a student table in clickhouse.

The query is here:

select * from ( 
with a as (select id from student)
select id from a
union all
select id from a
)

I am getting table Default.a doesnt exist (unknown table).

If I remove the select part which is after the union all, then query is running properly.

I allready tried enable_global_with_statement=1 setting and it isnt working.

Same query is running on postgres or mysql as expected.

Answer 1

Let's break down your query:

with a as (select id from student) -- First element of the UNION ALL
select id from a
union all
select id from a -- Second element of the UNION ALL
)

So you actually have the following elements:

with a as (select id from student) -- First element of the UNION ALL
select id from a

and

select id from a -- Second element of the UNION ALL

You can see that the CTE (WITH statement) belongs to only first part of the query. That's why you get table default.a doesnt exist because it is not defined for the second part.

If you want to have a CTE that can be accessible by all queries then you have to define it on top of all queries like this:

WITH a AS
    (
        SELECT number
        FROM numbers(10)
    )
SELECT *
FROM
(
    SELECT number
    FROM a
    UNION ALL
    SELECT number
    FROM a
)

Query id: 4330405d-a04b-423a-af47-618c62d09251

┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘
┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘