On Thu, Mar 2, 2017 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"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.
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.
From:
digoal@126.com Date: Subject:
[BUGS] BUG #14575: Standby recovery process call close() very slow,when drop (many small files) database on Primary.
Есть вопросы? Напишите нам!
Соглашаюсь с условиями обработки персональных данных
✖
By continuing to browse this website, you agree to the use of cookies. Go to Privacy Policy.