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

Commit91e16b9

Browse files
committed
Fix yet another corner case in dumping rules/views with USING clauses.
ruleutils.c tries to cope with additions/deletions/renamings of columns intables referenced by views, by means of adding machine-generated aliases tothe printed form of a view when needed to preserve the original semantics.A recent blog post by Marko Tiikkaja pointed out a case I'd missed though:if one input of a join with USING is itself a join, there is nothing tostop the user from adding a column of the same name as the USING column towhichever side of the sub-join didn't provide the USING column. And thenthere'll be an error when the view is re-parsed, since now the sub-joinexposes two columns matching the USING specification. We were catching alot of related cases, but not this one, so add some logic to cope with it.Back-patch to 9.3, which is the first release that makes any seriousattempt to cope with such cases (cf commit2ffa740 and follow-ons).
1 parent4c8aa8b commit91e16b9

File tree

3 files changed

+90
-11
lines changed

3 files changed

+90
-11
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 38 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -174,11 +174,14 @@ typedef struct
174174
* query to ensure it can be referenced unambiguously.
175175
*
176176
* Another problem is that a JOIN USING clause requires the columns to be
177-
* merged to have the same aliases in both input RTEs.To handle that, we do
178-
* USING-column alias assignment in a recursive traversal of the query's
179-
* jointree. When descending through a JOIN with USING, we preassign the
180-
* USING column names to the child columns, overriding other rules for column
181-
* alias assignment.
177+
* merged to have the same aliases in both input RTEs, and that no other
178+
* columns in those RTEs or their children conflict with the USING names.
179+
* To handle that, we do USING-column alias assignment in a recursive
180+
* traversal of the query's jointree. When descending through a JOIN with
181+
* USING, we preassign the USING column names to the child columns, overriding
182+
* other rules for column alias assignment. We also mark each RTE with a list
183+
* of all USING column names selected for joins containing that RTE, so that
184+
* when we assign other columns' aliases later, we can avoid conflicts.
182185
*
183186
* Another problem is that if a JOIN's input tables have had columns added or
184187
* deleted since the query was parsed, we must generate a column alias list
@@ -229,6 +232,9 @@ typedef struct
229232
/* This flag tells whether we should actually print a column alias list */
230233
boolprintaliases;
231234

235+
/* This list has all names used as USING names in joins above this RTE */
236+
List*parentUsing;/* names assigned to parent merged columns */
237+
232238
/*
233239
* If this struct is for a JOIN RTE, we fill these fields during the
234240
* set_using_names() pass to describe its relationship to its child RTEs.
@@ -306,7 +312,8 @@ static void set_deparse_for_query(deparse_namespace *dpns, Query *query,
306312
List*parent_namespaces);
307313
staticvoidset_simple_column_names(deparse_namespace*dpns);
308314
staticboolhas_dangerous_join_using(deparse_namespace*dpns,Node*jtnode);
309-
staticvoidset_using_names(deparse_namespace*dpns,Node*jtnode);
315+
staticvoidset_using_names(deparse_namespace*dpns,Node*jtnode,
316+
List*parentUsing);
310317
staticvoidset_relation_column_names(deparse_namespace*dpns,
311318
RangeTblEntry*rte,
312319
deparse_columns*colinfo);
@@ -2726,7 +2733,7 @@ set_deparse_for_query(deparse_namespace *dpns, Query *query,
27262733
* Select names for columns merged by USING, via a recursive pass over
27272734
* the query jointree.
27282735
*/
2729-
set_using_names(dpns, (Node*)query->jointree);
2736+
set_using_names(dpns, (Node*)query->jointree,NIL);
27302737
}
27312738

27322739
/*
@@ -2860,9 +2867,12 @@ has_dangerous_join_using(deparse_namespace *dpns, Node *jtnode)
28602867
*
28612868
* Column alias info is saved in the dpns->rtable_columns list, which is
28622869
* assumed to be filled with pre-zeroed deparse_columns structs.
2870+
*
2871+
* parentUsing is a list of all USING aliases assigned in parent joins of
2872+
* the current jointree node. (The passed-in list must not be modified.)
28632873
*/
28642874
staticvoid
2865-
set_using_names(deparse_namespace*dpns,Node*jtnode)
2875+
set_using_names(deparse_namespace*dpns,Node*jtnode,List*parentUsing)
28662876
{
28672877
if (IsA(jtnode,RangeTblRef))
28682878
{
@@ -2874,7 +2884,7 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
28742884
ListCell*lc;
28752885

28762886
foreach(lc,f->fromlist)
2877-
set_using_names(dpns, (Node*)lfirst(lc));
2887+
set_using_names(dpns, (Node*)lfirst(lc),parentUsing);
28782888
}
28792889
elseif (IsA(jtnode,JoinExpr))
28802890
{
@@ -2954,6 +2964,9 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
29542964
*/
29552965
if (j->usingClause)
29562966
{
2967+
/* Copy the input parentUsing list so we don't modify it */
2968+
parentUsing=list_copy(parentUsing);
2969+
29572970
/* USING names must correspond to the first join output columns */
29582971
expand_colnames_array_to(colinfo,list_length(j->usingClause));
29592972
i=0;
@@ -2983,6 +2996,7 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
29832996

29842997
/* Remember selected names for use later */
29852998
colinfo->usingNames=lappend(colinfo->usingNames,colname);
2999+
parentUsing=lappend(parentUsing,colname);
29863000

29873001
/* Push down to left column, unless it's a system column */
29883002
if (leftattnos[i]>0)
@@ -3002,9 +3016,13 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
30023016
}
30033017
}
30043018

3019+
/* Mark child deparse_columns structs with correct parentUsing info */
3020+
leftcolinfo->parentUsing=parentUsing;
3021+
rightcolinfo->parentUsing=parentUsing;
3022+
30053023
/* Now recursively assign USING column names in children */
3006-
set_using_names(dpns,j->larg);
3007-
set_using_names(dpns,j->rarg);
3024+
set_using_names(dpns,j->larg,parentUsing);
3025+
set_using_names(dpns,j->rarg,parentUsing);
30083026
}
30093027
else
30103028
elog(ERROR,"unrecognized node type: %d",
@@ -3471,6 +3489,15 @@ colname_is_unique(char *colname, deparse_namespace *dpns,
34713489
return false;
34723490
}
34733491

3492+
/* Also check against names already assigned for parent-join USING cols */
3493+
foreach(lc,colinfo->parentUsing)
3494+
{
3495+
char*oldname= (char*)lfirst(lc);
3496+
3497+
if (strcmp(oldname,colname)==0)
3498+
return false;
3499+
}
3500+
34743501
return true;
34753502
}
34763503

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1298,6 +1298,40 @@ select pg_get_viewdef('vv5', true);
12981298
JOIN tt10 USING (x);
12991299
(1 row)
13001300

1301+
--
1302+
-- Another corner case is that we might add a column to a table below a
1303+
-- JOIN USING, and thereby make the USING column name ambiguous
1304+
--
1305+
create table tt11 (x int, y int);
1306+
create table tt12 (x int, z int);
1307+
create table tt13 (z int, q int);
1308+
create view vv6 as select x,y,z,q from
1309+
(tt11 join tt12 using(x)) join tt13 using(z);
1310+
select pg_get_viewdef('vv6', true);
1311+
pg_get_viewdef
1312+
---------------------------
1313+
SELECT tt11.x, +
1314+
tt11.y, +
1315+
tt12.z, +
1316+
tt13.q +
1317+
FROM tt11 +
1318+
JOIN tt12 USING (x) +
1319+
JOIN tt13 USING (z);
1320+
(1 row)
1321+
1322+
alter table tt11 add column z int;
1323+
select pg_get_viewdef('vv6', true);
1324+
pg_get_viewdef
1325+
------------------------------
1326+
SELECT tt11.x, +
1327+
tt11.y, +
1328+
tt12.z, +
1329+
tt13.q +
1330+
FROM tt11 tt11(x, y, z_1)+
1331+
JOIN tt12 USING (x) +
1332+
JOIN tt13 USING (z);
1333+
(1 row)
1334+
13011335
-- clean up all the random objects we made above
13021336
set client_min_messages = warning;
13031337
DROP SCHEMA temp_view_test CASCADE;

‎src/test/regress/sql/create_view.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -417,6 +417,24 @@ alter table tt9 drop column xx;
417417

418418
select pg_get_viewdef('vv5', true);
419419

420+
--
421+
-- Another corner case is that we might add a column to a table below a
422+
-- JOIN USING, and thereby make the USING column name ambiguous
423+
--
424+
425+
createtablett11 (xint, yint);
426+
createtablett12 (xint, zint);
427+
createtablett13 (zint, qint);
428+
429+
createviewvv6asselect x,y,z,qfrom
430+
(tt11join tt12 using(x))join tt13 using(z);
431+
432+
select pg_get_viewdef('vv6', true);
433+
434+
altertable tt11 add column zint;
435+
436+
select pg_get_viewdef('vv6', true);
437+
420438
-- clean up all the random objects we made above
421439
set client_min_messages= warning;
422440
DROPSCHEMA temp_view_test CASCADE;

0 commit comments

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