Re: [BUGS] Re: BUG #8444: ERROR: table name tblb specified more than once in subquery
David Johnston pol...@yahoo.com 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: BUG #8444: ERROR: table name tblb specified more than once in subquery
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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: BUG #8444: ERROR: table name tblb specified more than once in subquery
A much more simple example courtesy of Chris Travers from the original -general thread that I suggested be moved to -bugs. 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; u | u | v ---+---+--- chris | chris | chris (1 row) On 9.3 it fails: ERROR: table name u specified more than once It may be a silly example but it works. -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8444-ERROR-table-name-tblb-specified-more-than-once-in-subquery-tp5770540p5770654.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
