Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit648bd05

Browse files
committed
Re-allow duplicate aliases within aliased JOINs.
Although the SQL spec forbids duplicate table aliases, historicallywe've allowed queries like SELECT ... FROM tab1 x CROSS JOIN (tab2 x CROSS JOIN tab3 y) zon the grounds that the aliased join (z) hides the aliases within it,therefore there is no conflict between the two RTEs named "x". TheLATERAL patch broke this, on the misguided basis that "x" could beambiguous if tab3 were a LATERAL subquery. To avoid breaking existingqueries, it's better to allow this situation and complain only iftab3 actually does contain an ambiguous reference. We need only removethe check that was throwing an error, because the column lookup codeis already prepared to handle ambiguous references. Per bug #8444.
1 parent705556a commit648bd05

File tree

4 files changed

+52
-4
lines changed

4 files changed

+52
-4
lines changed

‎src/backend/parser/parse_clause.c

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -720,14 +720,15 @@ transformFromClauseItem(ParseState *pstate, Node *n,
720720
* we always push them into the namespace, but mark them as not
721721
* lateral_ok if the jointype is wrong.
722722
*
723+
* Notice that we don't require the merged namespace list to be
724+
* conflict-free. See the comments for scanNameSpaceForRefname().
725+
*
723726
* NB: this coding relies on the fact that list_concat is not
724727
* destructive to its second argument.
725728
*/
726729
lateral_ok= (j->jointype==JOIN_INNER||j->jointype==JOIN_LEFT);
727730
setNamespaceLateralState(l_namespace, true,lateral_ok);
728731

729-
checkNameSpaceConflicts(pstate,pstate->p_namespace,l_namespace);
730-
731732
sv_namespace_length=list_length(pstate->p_namespace);
732733
pstate->p_namespace=list_concat(pstate->p_namespace,l_namespace);
733734

‎src/backend/parser/parse_relation.c

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -130,6 +130,18 @@ refnameRangeTblEntry(ParseState *pstate,
130130
* Search the query's table namespace for an RTE matching the
131131
* given unqualified refname. Return the RTE if a unique match, or NULL
132132
* if no match. Raise error if multiple matches.
133+
*
134+
* Note: it might seem that we shouldn't have to worry about the possibility
135+
* of multiple matches; after all, the SQL standard disallows duplicate table
136+
* aliases within a given SELECT level. Historically, however, Postgres has
137+
* been laxer than that. For example, we allow
138+
*SELECT ... FROM tab1 x CROSS JOIN (tab2 x CROSS JOIN tab3 y) z
139+
* on the grounds that the aliased join (z) hides the aliases within it,
140+
* therefore there is no conflict between the two RTEs named "x". However,
141+
* if tab3 is a LATERAL subquery, then from within the subquery both "x"es
142+
* are visible. Rather than rejecting queries that used to work, we allow
143+
* this situation, and complain only if there's actually an ambiguous
144+
* reference to "x".
133145
*/
134146
staticRangeTblEntry*
135147
scanNameSpaceForRefname(ParseState*pstate,constchar*refname,intlocation)
@@ -174,8 +186,7 @@ scanNameSpaceForRefname(ParseState *pstate, const char *refname, int location)
174186
/*
175187
* Search the query's table namespace for a relation RTE matching the
176188
* given relation OID.Return the RTE if a unique match, or NULL
177-
* if no match. Raise error if multiple matches (which shouldn't
178-
* happen if the namespace was checked correctly when it was created).
189+
* if no match. Raise error if multiple matches.
179190
*
180191
* See the comments for refnameRangeTblEntry to understand why this
181192
* acts the way it does.

‎src/test/regress/expected/join.out

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3093,6 +3093,24 @@ SELECT * FROM
30933093
(5 rows)
30943094

30953095
rollback;
3096+
-- bug #8444: we've historically allowed duplicate aliases within aliased JOINs
3097+
select * from
3098+
int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1; -- error
3099+
ERROR: column reference "f1" is ambiguous
3100+
LINE 2: ..._tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = f1;
3101+
^
3102+
select * from
3103+
int8_tbl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1; -- error
3104+
ERROR: invalid reference to FROM-clause entry for table "y"
3105+
LINE 2: ...bl x join (int4_tbl x cross join int4_tbl y) j on q1 = y.f1;
3106+
^
3107+
HINT: There is an entry for table "y", but it cannot be referenced from this part of the query.
3108+
select * from
3109+
int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on q1 = f1; -- ok
3110+
q1 | q2 | f1 | ff
3111+
----+----+----+----
3112+
(0 rows)
3113+
30963114
--
30973115
-- Test LATERAL
30983116
--
@@ -3947,6 +3965,12 @@ ERROR: invalid reference to FROM-clause entry for table "a"
39473965
LINE 1: ...m int4_tbl a full join lateral generate_series(0, a.f1) g on...
39483966
^
39493967
DETAIL: The combining JOIN type must be INNER or LEFT for a LATERAL reference.
3968+
-- check we complain about ambiguous table references
3969+
select * from
3970+
int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss);
3971+
ERROR: table reference "x" is ambiguous
3972+
LINE 2: ...cross join (int4_tbl x cross join lateral (select x.f1) ss);
3973+
^
39503974
-- LATERAL can be used to put an aggregate into the FROM clause of its query
39513975
select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
39523976
ERROR: aggregate functions are not allowed in FROM clause of their own query level

‎src/test/regress/sql/join.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -892,6 +892,15 @@ SELECT * FROM
892892

893893
rollback;
894894

895+
-- bug #8444: we've historically allowed duplicate aliases within aliased JOINs
896+
897+
select*from
898+
int8_tbl xjoin (int4_tbl xcross join int4_tbl y) jon q1= f1;-- error
899+
select*from
900+
int8_tbl xjoin (int4_tbl xcross join int4_tbl y) jon q1=y.f1;-- error
901+
select*from
902+
int8_tbl xjoin (int4_tbl xcross join int4_tbl y(ff)) jon q1= f1;-- ok
903+
895904
--
896905
-- Test LATERAL
897906
--
@@ -1079,5 +1088,8 @@ select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
10791088
-- SQL:2008 says the left table is in scope but illegal to access here
10801089
select f1,gfrom int4_tbl aright join lateral generate_series(0,a.f1) gon true;
10811090
select f1,gfrom int4_tbl a fulljoin lateral generate_series(0,a.f1) gon true;
1091+
-- check we complain about ambiguous table references
1092+
select*from
1093+
int8_tbl xcross join (int4_tbl xcross join lateral (selectx.f1) ss);
10821094
-- LATERAL can be used to put an aggregate into the FROM clause of its query
10831095
select1from tenk1 a, lateral (selectmax(a.unique1)from int4_tbl b) ss;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp