Re: BUG #14573: lateral joins, ambuiguity

Thank you for the clarification.

Denise

On Thu, Mar 2, 2017 at 11:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Wed, Mar 1, 2017 at 8:22 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >> On Wednesday, March 1, 2017, <dlw405(at)gmail(dot)com> wrote:
> >>> The LATERAL JOIN has access to all previous columns in the join, but,
> it
> >>> doesn't give an error when there are two columns of the same name.
> >>> Instead, it silently selects the first column.
>
> The above statement is demonstrably false, for example
>
> regression=# create table t1 (f1 int, f2 int);
> CREATE TABLE
> regression=# select * from t1 a cross join t1 b cross join lateral (select
> f1) ss;
> ERROR: column reference "f1" is ambiguous
> LINE 1: ...from t1 a cross join t1 b cross join lateral (select f1) ss;
> ^
>
> David has the correct analysis:
>
> >> IIUC the preference exhibited is an explicit column present on the left
> >> side of the join over the implicit relation named column within its own
> >> query.
>
> An unqualified name is first sought as a column reference, and only if
> that fails altogether do we check whether it could be interpreted as a
> whole-row reference to some table.
>
> > ​I'd say its working as designed (or, at least, its not unique to
> LATERAL)
> > - though no joy on finding where its end-user documented.​
>
> It's mentioned here:
> https://www.postgresql.org/docs/9.5/static/rowtypes.html#ROWTYPES-USAGE
>
> Note however that simple names are matched to column names before
> table names, so this example works only because there is no column
> named c in the query's tables.
>
> and a bit further down
>
> Even though .* does nothing in such cases, using it is good style,
> since it makes clear that a composite value is intended. In
> particular, the parser will consider c in c.* to refer to a table
> name or alias, not to a column name, so that there is no
> ambiguity; whereas without .*, it is not clear whether c means a
> table name or a column name, and in fact the column-name
> interpretation will be preferred if there is a column named c.
>
> (Admittedly, that whole section is of pretty recent vintage; but the
> behavior it describes is old.)
>
> >>> We are confused on why there was not an ambiguity error thrown on the
> >>> property 'owner' during the 2nd lateral join's SELECT statement. Should
> >>> there be?
>
> We can't do that because interpreting "foo" as a table reference is not
> per SQL standard. If there's a single possible interpretation as a
> column, whether it be plain or LATERAL or outer-query, we have to
> resolve it that way without complaint, or we will fail to accept
> standard-compliant queries.
>
> The whole business of allowing a table name without ".*" decoration is
> a PostQUEL-ism that we inherited from Berkeley and never removed; but
> it's nonstandard and somewhat deprecated because of the ambiguity.
>
> regards, tom lane
>