Amos Bird, [07.09.20 05:11]
BTW, what do you think of having CTE in clickhouse?

Currently the WITH statement can only introduce scalars and the grammar is of reversed order of SQL's CTE. Perhaps we can provide support like

WITH <name> AS (subquery)
while still keep the old "CSE" syntax.

Alexey Milovidov, [07.09.20 05:16]
Yes, we need to support it for FROM, IN, etc. And it should be easy to do.

Amos Bird, [07.09.20 05:17]
hmm, is the syntax ok? I was under the impression that it might introduce confusions to users. But now I feel that there might not be better ways...

Alexey Milovidov, [07.09.20 05:18]
As it's compatible with CTE, it's Ok.

We can also support

WITH (subquery) AS name 

just for consistency with existing syntax.
Because we can disambiguate these variants.

Amos Bird, [13.09.20 14:24]
> For the reference (that both x AS subquery and subquery AS x are Ok):

Hmm, do you want both variants to be treated exactly the same? Currently I left the old one's behavior intact..

Alexey Milovidov, [13.09.20 14:25]
It is treat as scalar subquery?

Amos Bird, [13.09.20 14:25]
yes

Alexey Milovidov, [13.09.20 14:26]
But there's no point to have scalar subquery in FROM
and almost no point to have it in IN.

Alexey Milovidov, [13.09.20 14:26]
Is there any special cases if both variants will work the same?

Amos Bird, [13.09.20 14:27]
[In reply to Alexey Milovidov]
oh, no.  I mean with (scalar subquery) as name   and with name as (general subquery)

Amos Bird, [13.09.20 14:28]
hmm, I think I will extend it a bit, to have both variants define general subqueries

Amos Bird, [13.09.20 14:28]
not sure if there would be consequences

Alexey Milovidov, [13.09.20 14:29]
Maybe both variants can work exactly as if subquery is substituted - no matter it will be scalar or general subquery?

Example:

WITH (SELECT 1) AS x SELECT x
WITH (SELECT 1) AS x SELECT * FROM x
WITH x AS (SELECT 1) SELECT x
WITH x AS (SELECT 1) SELECT * FROM x

Amos Bird, [13.09.20 14:29]
Yes, it's possible. I need to reason it a bit. There might be tricky corner cases

Alexey Milovidov, [13.09.20 14:30]
Ok.

Amos Bird, [13.09.20 14:33]
It means x is always a table (not a column)

Amos Bird, [13.09.20 14:33]
it will break something I guess

Alexey Milovidov, [13.09.20 14:34]
[In reply to Amos Bird]
In what query?

Amos Bird, [13.09.20 14:37]
http://sqlfiddle.com/#!17/e98d3/1/0

Amos Bird, [13.09.20 14:37]
The visibility rule is different

Amos Bird, [13.09.20 14:39]
It appears to me that separating CTE and CSE might be a good design

Alexey Milovidov, [13.09.20 14:46]
In this example there is ambiguity. We can stick to some behaviour in case of ambiguous names - and it's ok even if it does not correspond to Postgres.

It's also Ok to separate CTE. Let's keep your variant. You can also copy-paste all this discussion "for the reference".

Amos Bird, [13.09.20 14:48]
[In reply to Alexey Milovidov]
Ok. I like the idea of separating CTE because in ClickHouse, subqueries have two basic forms, and scalar variant is exactly the same as other expressions used in the WITH statement. It's more consistent. So if we write WITH (subquery) as a ..., a can be used anywhere that other exprs are used, and vice versa

Alexey Milovidov, [13.09.20 14:49]
Good, I like the reasoning :)