Re: [BUGS] BUG #14573: lateral joins, ambuiguity - Mailing list pgsql-bugs

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Mar 1, 2017 at 8:22 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>> On Wednesday, March 1, 2017, <dlw405@gmail.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs