`WITH` statement inside subqueries

[BlueskyFR]

Company or project name

No response

Question

Hi! I noticed a strange behavior of the WITH statement when using subqueries.
For some reason this doesn't work:

with
    A = 2 as selector
select sum(value) from (
    select * from test_table where selector
    
    union all
    
    select * from test_table
)

While this DOES:

+select sum(value) from (
with
    A = 2 as selector
-select sum(value) from (
    select * from test_table where selector
    
    union all
    
    select * from test_table
)

I really want to write it the first way because I want to access some stuff in the global scope and it is less convenient to nest the WITH statements in my opinion.

So is it a behavior I did not get? If so, is that explained somewhere in the docs?

Thanks in advance 😊

[den-crane]

IMHO

This should not work https://fiddle.clickhouse.com/7b765ba1-61be-4f00-87ef-40fe963d258f

with
    A = 2 as selector
    select * from (select 2 A) where selector
---
2

it's a bug.

A is not defined in the scope of with.

[BlueskyFR]

A is the name of the table column.
In this example from the docs, bytes doesn't exist either for instance:

[den-crane]

A is the name of the table column.
In this example from the docs, bytes doesn't exist either for instance:

IMHO it's a bug in the documentation as well.

It's was available / possible before the analyzer.

[BlueskyFR]

A is the name of the table column.
In this example from the docs, bytes doesn't exist either for instance:

IMHO it's a bug in the documentation as well.

It's was available / possible before the analyzer.

What do you mean?
What changed? And what should we do now?

[den-crane]

I mean:

https://fiddle.clickhouse.com/74694a62-0643-4179-9f51-e469e20d9919

https://clickhouse.com/docs/en/operations/analyzer

And what should we do now?

Adapt ?

[BlueskyFR]

Oh I didn't know about CH having a new analyzer, I just started learning about CH a couple days ago.

So what exactly can we put in WITH that actually avoid repeating code? Because I want to be able to put more than just queries inside it if possible, and now I feel a bit lost.

And is with limited to a certain scope also?

[BlueskyFR]

I read the docs another time and it is totally unclear to me what we are allowed to put in a WITH statement except subqueries.
Would it be possible to either link to a more comprehensive doc page or update it?

The page says it can be used to act like variables/aliases but you seem to indicate it can only store subqueries and constant expressions with no external dependencies, which make them way worse than what they could be.

[den-crane]

So what exactly can we put in WITH that actually avoid repeating code?

Can you please show an example of a repetition?

[den-crane]

I read the docs another time and it is totally unclear to me what we are allowed to put in a WITH statement except subqueries. Would it be possible to either link to a more comprehensive doc page or update it?

The page says it can be used to act like variables/aliases but you seem to indicate it can only store subqueries and constant expressions with no external dependencies, which make them way worse than what they could be.

There is no clear explanation, see #62946 (comment) #71659