Обсуждение: conditional query in where has name collision. bug?

<div dir="ltr">This a toy example from a 'upsert' script that appends new data:<br /><br />select a from (select  1 as
a)as t1 where not exists (select true from (select 2 as a) t2 where a=a) ;<br /> a <br />───<br />(0 rows)<br /><br
/>selecta from (select  1 as a) as t1 where not exists (select true from (select 2 as a) t2 where t1.a=t2.a)  ;<br /> a
<br/>───<br /> 1<br />(1 row)<br /><br />Please tell me this a bug. We can see in the first query without naming the
tablesthat 'a=a' uses table t2 for both columns. Luckily,  the incorrect first query fails from primary key conditions
inreal life. This goes against the implied convention that when in doubt raise an error. It is also a very idiomatic
wayto to real life inserts. So, I cannot believe that I am the first to find this.  The behavior reminds me of old
schoolmysql: lets allow an int and a string to add and just cast the string as an int because that must be what the
userwants and will be convenient.<br /></div> 

>>>>> "bill" == bill wilson <bill.wilson.home@gmail.com> 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
notexists (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
forbothbill> 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 <identifier chain> in sql2008).

--
Andrew (irc:RhodiumToad)