Re: BUG #8444: ERROR: table name "tblb" specified more than once in subquery

From: David Johnston <polobo(at)yahoo(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #8444: ERROR: table name "tblb" specified more than once in subquery
Date: 2013-09-13 15:41:31
Message-ID: 1379086891857-5770710.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tom Lane-2 wrote
> David Johnston &lt;

> polobo@

> &gt; writes:
>>> Here is a minimal query that demonstrates the problem. In 9.1 it works:
>>>
>>> chris=# select * FROM current_user u join (current_user u cross join
>>> current_user v) x on true;
>>>
>>> On 9.3 it fails:
>>> ERROR: table name "u" specified more than once
>
> This is an intentional change that came in with the LATERAL feature.
> The query is illegal per SQL spec but we used to allow it anyway,
> on the theory that the table name "u" inside the aliased join "x"
> wasn't visible anywhere that the other "u" was visible, so the
> duplicate alias name was harmless. But in the presence of LATERAL
> it's not harmless; consider
>
> select * FROM current_user u join
> (current_user u cross join LATERAL (select u.x) v) x on true;
>
> Which instance of "u" does the lateral reference refer to?
>
> (I think there was some discussion of this in the pgsql-hackers list
> about a year ago, but I couldn't find it in a desultory search.)
>
> regards, tom lane

I do vaguely recall that said discussion exists. However, this and the
-general thread for the same issue both seem to indicate that the actual
order of the joining affects whether the error is thrown...I guess the way
LATERAL works this does make sense - somewhat.

While the behavior is intentional not mentioning it in the release notes,
section

E.1.2. Migration to Version 9.3

is an oversight that should be corrected.

Might be worth finding and linking to the thread in the release notes so
that people affected by this change go and look to figure out why it was
made. Given your example involves a LATERAL sub-clause my first thought is
that any non-LATERAL (and thus all previous version) queries would be
unaffected.

If I find a link I'll come back and post it for reference from here and
-general at least.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8444-ERROR-table-name-tblb-specified-more-than-once-in-subquery-tp5770540p5770710.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message stephane.wustner 2013-09-13 16:07:15 BUG #8451: quantile extension: memory corruption?
Previous Message Tom Lane 2013-09-13 15:20:00 Re: Re: BUG #8444: ERROR: table name "tblb" specified more than once in subquery