Alias replacement is working inconsitantly

[crakjie]

I got surprising result with the usage of with when replacing aliases. (version v23.8.9)

for exemple I was expecting this query to return 1

with dummy + 1 as dummy select dummy from system.one

But clickhouse seems to ignore the dummy in the with and return 0

WITH dummy + 1 AS dummy
SELECT dummy
FROM system.one


┌─dummy─┐
│     0 │
└───────┘

1 row in set. Elapsed: 0.001 sec. 

Strangly when running this query SELECT dummy + 1 as dummy FROM system.one I correctly got 1
But both query should (imo) have the same semantic

Another wierd case is this query :
with dummy + 1 as dummy select dummy + 1 as dummy , dummy from system.one
I'm not sure such case must be resolved but the return is quite strange, look at the result of clikchouse parsing.

WITH dummy + 1 AS dummy
SELECT
    dummy,
    dummy
FROM system.one


┌─dummy─┬─dummy─┐
│     1 │     1 │
└───────┴───────┘

If we rerun the result of the parsing we got the result with 0.

I'm not sure wich solution should be the prefered one, but for sure it should be consitant.

┌─getSetting('prefer_column_name_to_alias')─┐
│ false                                     │
└───────────────────────────────────────────┘
┌─getSetting('enable_global_with_statement')─┐
│ false                                      │
└────────────────────────────────────────────┘
┌─getSetting('optimize_respect_aliases')─┐
│ false                                  │
└────────────────────────────────────────┘

[den-crane]

check select getSetting('prefer_column_name_to_alias')

see https://clickhouse.com/docs/en/operations/settings/settings#prefer-column-name-to-alias
https://fiddle.clickhouse.com/be4f5093-c7f2-4122-bbe2-8c820a7b5566

[den-crane]

If we rerun the result of the parsing we got the result with 0.

No reproduce: https://fiddle.clickhouse.com/265945f0-16cc-4f2c-94a2-8025a5692812

[den-crane]

Also please share select name,value from system.settings where changed

[crakjie]

I have another exemple

WITH dummy + 3 AS dummy
SELECT dummy + 1 AS y
FROM system.one
SETTINGS enable_global_with_statement = 1

Return

┌─y─┐
│ 1 │
└───┘

WITH dummy + 3 AS dummy
SELECT dummy + 1 AS y
FROM system.one
SETTINGS enable_global_with_statement = 1, prefer_column_name_to_alias = 1

as well as

WITH dummy + 3 AS dummy
SELECT dummy + 1 AS y
FROM system.one
SETTINGS enable_global_with_statement = 0, prefer_column_name_to_alias = 0

for version https://fiddle.clickhouse.com/dc9229ae-2b1b-4cbe-8c68-3c3b45e07e8d

It's returning 1 up to the version 24.2.2.71 and return 4 from the version 24.3

So this bug seems to be fixed in the latest version.
The 24.3 result look to me more correct

WITH dummy + 3 AS dummy
SELECT dummy + 1 AS y
FROM system.one
SETTINGS allow_experimental_analyzer = 1

Query id: fbfe7463-1fc1-4fd5-be11-f4d5fc304301

┌─y─┐
│ 4 │
└───┘

So the new interpeter become production ready?

[Algunenano]

So the new interpeter become production ready?

It is the default now in 24.3+