Given this query snowflake returns a result set of 2, arbitrarily resolving y to the table T,
select y
from (select 1 x, 2 y) T
join (select 1 x, 3 y) T1 using (x)
while at the same time returning an ambiguous column error when using a qualified join instead:
select y
from (select 1 x, 2 y) T
join (select 1 x, 3 y) T1 on T.x = T1.x
What's the set of rules that determine whether a column reference is ambiguous in Snowflake SQL? Postgres considers both of these queries ambiguous.
This answer is just an observation. It seems the column is chosen depending on order of join(left-to-right):
CREATE OR REPLACE TABLE T(x INT, y INT) AS select 1, 2 UNION SELECT 10, 20;
CREATE OR REPLACE TABLE T1(x INT, y INT) AS select 1, 3 UNION SELECT 10, 30;
-- disabling cache
ALTER SESSION SET USE_CACHED_RESULT=FALSE;
Query profile:
explain using tabular
select y
from T
join T1 using (x);
Output:
Swapped join order:
explain using tabular
select y
from T1
join T using (x);
Output:
