Re: conditional query in where has name collision. bug?
Re: conditional query in where has name collision. bug?
От:
Andrew Gierth <andrew@tao11.riddles.org.uk>
Дата:
>>>>> "bill" == bill wilson writes: bill> This a toy example from a 'upsert' script that appends new data:bill> select a from (select 1 as a) as t1 where not exists (selectbill> true from (select 2 as a) t2 where a=a) ;bill> abill> ───bill> (0 rows) bill> Please tell me this a bug. We can see in the first querybill> without naming the tables that 'a=a' uses table t2 for bothbill> columns. Of course it's not a bug. The column "a" in the inner scope hides the column "a" in the outer scope, as explicitly required by the spec (6.6 in sql2008). -- Andrew (irc:RhodiumToad)
conditional query in where has name collision. bug?
От:
bill wilson <bill.wilson.home@gmail.com>
Дата:
This a toy example from a 'upsert' script that appends new data:
select a from (select 1 as a) as t1 where not exists (select true from (select 2 as a) t2 where a=a) ;
a
───
(0 rows)
select a from (select 1 as a) as t1 where not exists (select true from (select 2 as a) t2 where t1.a=t2.a) ;
a
───
1
(1 row)
Please tell me this a bug. We can see in the first query without naming the tables that 'a=a' uses table t2 for both columns. Luckily, the incorrect first query fails from primary key conditions in real life. This goes against the implied convention that when in doubt raise an error. It is also a very idiomatic way to to real life inserts. So, I cannot believe that I am the first to find this. The behavior reminds me of old school mysql: lets allow an int and a string to add and just cast the string as an int because that must be what the user wants and will be convenient.
select a from (select 1 as a) as t1 where not exists (select true from (select 2 as a) t2 where a=a) ;
a
───
(0 rows)
select a from (select 1 as a) as t1 where not exists (select true from (select 2 as a) t2 where t1.a=t2.a) ;
a
───
1
(1 row)
Please tell me this a bug. We can see in the first query without naming the tables that 'a=a' uses table t2 for both columns. Luckily, the incorrect first query fails from primary key conditions in real life. This goes against the implied convention that when in doubt raise an error. It is also a very idiomatic way to to real life inserts. So, I cannot believe that I am the first to find this. The behavior reminds me of old school mysql: lets allow an int and a string to add and just cast the string as an int because that must be what the user wants and will be convenient.
