This query works in one account but throws an error (below) in another. Is there a parameter that affects this behaviour? Both accounts have the same Snowflake version (6.41.2).
with cte_one as (
select 'aaa' a
,'bbb' b
)
select t1.a || t1.b as c
from cte_one t1
join cte_one t2 on c = t2.a || t2.b
;
This will work.
with cte_one as (
select 'aaa' a
,'bbb' b
), t3 as
(
select t1.a || t1.b as c
from cte_one t1
)
select * from t3
join cte_one t2 on c = t2.a || t2.b
;
| C | A | B |
|---|---|---|
| aaabbb | aaa | bbb |
Virtual column C doesn't exist yet in the original syntax when the compiler goes into the join. As humans we can read what the original SQL is trying to do and it makes sense. The compiler sees it differently.
Out of curiosity I went to SQL Fiddle I tried the original SQL on Oracle, Postgres, and MS SQL Server. They all reported errors saying C does not exist.
The provided code sample works as-is:
Environment:
SELECT CURRENT_REGION(), CURRENT_VERSION();
-- AZURE_WESTEUROPE 6.41.2
Query profile:
explain using tabular
with cte_one as (
select 'aaa' a ,'bbb' b union all select 'x', 'y'
)
select t1.a || t1.b as c, 1
from cte_one t1
join cte_one t2
on c = t2.a || t2.b;
Output:
There is a feature for resolving aliases from the select list in the join predicate. The rollout process was not completed yet for all accounts. You should engage Snowflake Support and ask them to fix the configuration discrepancies on your accounts.
I would suggest disabling it on both accounts and using Greg's workaround.
