conditional query in where has name collision. bug?

From: bill wilson <bill(dot)wilson(dot)home(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: conditional query in where has name collision. bug?
Date: 2014-09-20 21:03:18
Message-ID: CA+baN9wZhf890hiKXuWTiZO+Yh5LA5OHORfYifcKC2jDOxN3tg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Gierth 2014-09-21 00:08:59 Re: conditional query in where has name collision. bug?
Previous Message Christoph Berg 2014-09-20 20:13:30 Re: Hide 'Execution time' in EXPLAIN (COSTS OFF)