Re: BUG #14573: lateral joins, ambuiguity

"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