We had a complaint
http://www.postgresql.org/message-id/E1VJuBy-0002a1-Qv@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?
regards, tom lane
