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.
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) |