Re: Relax table alias conflict rule in 9.3?

On Sun, Nov 10, 2013 at 8:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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?

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