conditional query in where has name collision. bug?

Started by bill wilsonover 11 years ago2 messages
#1bill 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.

#2Andrew Gierth
andrew@tao11.riddles.org.uk
In reply to: bill wilson (#1)
Re: conditional query in where has name collision. bug?

"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 not exists (select
bill> true from (select 2 as a) t2 where a=a) ;
bill> a
bill> ───
bill> (0 rows)

bill> Please tell me this a bug. We can see in the first query
bill> without naming the tables that 'a=a' uses table t2 for both
bill> 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)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers