On Sun, Nov 10, 2013 at 8:06 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > We had a complaint > http://www.postgresql.org/message-id/e1vjuby-0002a1...@wrigleys.postgresql.org > about the fact that 9.3 rejects queries with duplicate table aliases like > this: > > select * from tenk1 a left join (int4_tbl a cross join int8_tbl b) c on > unique1 = f1; > ERROR: table name "a" specified more than once > > I pushed back on this on the grounds that this is illegal per SQL spec: > the standard is pretty clear that you can't use the same table alias more > than once in a given level of SELECT (cf SQL:2008 7.6 <table reference>, > syntax rules 6 and 7). However, the complainant has a good point that if > we've accepted this since forever, ceasing to accept it is going to cause > people problems. Moreover, my argument that it makes things ambiguous for > LATERAL doesn't hold a lot of water. Duplicate table aliases were > potentially ambiguous before, too, but we allowed the case anyway and only > complained if there's actually an ambiguous reference. We could do the > same for LATERAL references. > > I poked into the code a bit and soon realized that the problem stems from > the checkNameSpaceConflicts call that I added to transformFromClauseItem > in the LATERAL patch (in HEAD, line 729 of parse_clause.c). That throws > an error if the left side of a JOIN exposes any aliases that are already > exposed at top level of the FROM clause. There's no such check pre-9.3, > and I'm not sure now why I thought it was appropriate here, since the > column-reference lookup code is perfectly capable of dealing with > ambiguous references. Taking out that call allows the above query to work > again, while changing no existing regression test results. Cases that are > actually ambiguous will throw an appropriate error: > > select * from tenk1 a left join (int4_tbl a cross join lateral (select a.f1) > b) c on unique1 = f1; > ERROR: table reference "a" is ambiguous > LINE 1: ... left join (int4_tbl a cross join lateral (select a.f1) b) ... > ^ > > This discovery also shows that there's nothing particularly principled > about 9.3's behavior, because it complains about > select * from tenk1 a left join (int4_tbl a cross join int8_tbl b) c on > unique1 = f1; > but it's perfectly happy with > select * from tenk1 a left join (int4_tbl b cross join int8_tbl a) c on > unique1 = f1; > > So I propose removing that call, adding regression tests like these > examples, and back-patching to 9.3. Any objections?
I see that you already did this, so perhaps it's moot, but FWIW, +1 from me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
