From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | "dlw405(at)gmail(dot)com" <dlw405(at)gmail(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14573: lateral joins, ambuiguity |
Date: | 2017-03-02 19:25:22 |
Message-ID: | 18204.1488482722@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2017-03-02 23:22:59 | Re: BUG #14543: libpq fails with group readable ssl keys |
Previous Message | Masahiko Sawada | 2017-03-02 17:16:32 | Two phase commit in ECPG |