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

Commite31193d

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 parentb72e90b commite31193d

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
@@ -172,11 +172,14 @@ typedef struct
172172
* query to ensure it can be referenced unambiguously.
173173
*
174174
* Another problem is that a JOIN USING clause requires the columns to be
175-
* merged to have the same aliases in both input RTEs.To handle that, we do
176-
* USING-column alias assignment in a recursive traversal of the query's
177-
* jointree. When descending through a JOIN with USING, we preassign the
178-
* USING column names to the child columns, overriding other rules for column
179-
* alias assignment.
175+
* merged to have the same aliases in both input RTEs, and that no other
176+
* columns in those RTEs or their children conflict with the USING names.
177+
* To handle that, we do USING-column alias assignment in a recursive
178+
* traversal of the query's jointree. When descending through a JOIN with
179+
* USING, we preassign the USING column names to the child columns, overriding
180+
* other rules for column alias assignment. We also mark each RTE with a list
181+
* of all USING column names selected for joins containing that RTE, so that
182+
* when we assign other columns' aliases later, we can avoid conflicts.
180183
*
181184
* Another problem is that if a JOIN's input tables have had columns added or
182185
* deleted since the query was parsed, we must generate a column alias list
@@ -227,6 +230,9 @@ typedef struct
227230
/* This flag tells whether we should actually print a column alias list */
228231
boolprintaliases;
229232

233+
/* This list has all names used as USING names in joins above this RTE */
234+
List*parentUsing;/* names assigned to parent merged columns */
235+
230236
/*
231237
* If this struct is for a JOIN RTE, we fill these fields during the
232238
* set_using_names() pass to describe its relationship to its child RTEs.
@@ -304,7 +310,8 @@ static void set_deparse_for_query(deparse_namespace *dpns, Query *query,
304310
List*parent_namespaces);
305311
staticvoidset_simple_column_names(deparse_namespace*dpns);
306312
staticboolhas_dangerous_join_using(deparse_namespace*dpns,Node*jtnode);
307-
staticvoidset_using_names(deparse_namespace*dpns,Node*jtnode);
313+
staticvoidset_using_names(deparse_namespace*dpns,Node*jtnode,
314+
List*parentUsing);
308315
staticvoidset_relation_column_names(deparse_namespace*dpns,
309316
RangeTblEntry*rte,
310317
deparse_columns*colinfo);
@@ -2579,7 +2586,7 @@ set_deparse_for_query(deparse_namespace *dpns, Query *query,
25792586
* Select names for columns merged by USING, via a recursive pass over
25802587
* the query jointree.
25812588
*/
2582-
set_using_names(dpns, (Node*)query->jointree);
2589+
set_using_names(dpns, (Node*)query->jointree,NIL);
25832590
}
25842591

25852592
/*
@@ -2713,9 +2720,12 @@ has_dangerous_join_using(deparse_namespace *dpns, Node *jtnode)
27132720
*
27142721
* Column alias info is saved in the dpns->rtable_columns list, which is
27152722
* assumed to be filled with pre-zeroed deparse_columns structs.
2723+
*
2724+
* parentUsing is a list of all USING aliases assigned in parent joins of
2725+
* the current jointree node. (The passed-in list must not be modified.)
27162726
*/
27172727
staticvoid
2718-
set_using_names(deparse_namespace*dpns,Node*jtnode)
2728+
set_using_names(deparse_namespace*dpns,Node*jtnode,List*parentUsing)
27192729
{
27202730
if (IsA(jtnode,RangeTblRef))
27212731
{
@@ -2727,7 +2737,7 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
27272737
ListCell*lc;
27282738

27292739
foreach(lc,f->fromlist)
2730-
set_using_names(dpns, (Node*)lfirst(lc));
2740+
set_using_names(dpns, (Node*)lfirst(lc),parentUsing);
27312741
}
27322742
elseif (IsA(jtnode,JoinExpr))
27332743
{
@@ -2807,6 +2817,9 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
28072817
*/
28082818
if (j->usingClause)
28092819
{
2820+
/* Copy the input parentUsing list so we don't modify it */
2821+
parentUsing=list_copy(parentUsing);
2822+
28102823
/* USING names must correspond to the first join output columns */
28112824
expand_colnames_array_to(colinfo,list_length(j->usingClause));
28122825
i=0;
@@ -2836,6 +2849,7 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
28362849

28372850
/* Remember selected names for use later */
28382851
colinfo->usingNames=lappend(colinfo->usingNames,colname);
2852+
parentUsing=lappend(parentUsing,colname);
28392853

28402854
/* Push down to left column, unless it's a system column */
28412855
if (leftattnos[i]>0)
@@ -2855,9 +2869,13 @@ set_using_names(deparse_namespace *dpns, Node *jtnode)
28552869
}
28562870
}
28572871

2872+
/* Mark child deparse_columns structs with correct parentUsing info */
2873+
leftcolinfo->parentUsing=parentUsing;
2874+
rightcolinfo->parentUsing=parentUsing;
2875+
28582876
/* Now recursively assign USING column names in children */
2859-
set_using_names(dpns,j->larg);
2860-
set_using_names(dpns,j->rarg);
2877+
set_using_names(dpns,j->larg,parentUsing);
2878+
set_using_names(dpns,j->rarg,parentUsing);
28612879
}
28622880
else
28632881
elog(ERROR,"unrecognized node type: %d",
@@ -3324,6 +3342,15 @@ colname_is_unique(char *colname, deparse_namespace *dpns,
33243342
return false;
33253343
}
33263344

3345+
/* Also check against names already assigned for parent-join USING cols */
3346+
foreach(lc,colinfo->parentUsing)
3347+
{
3348+
char*oldname= (char*)lfirst(lc);
3349+
3350+
if (strcmp(oldname,colname)==0)
3351+
return false;
3352+
}
3353+
33273354
return true;
33283355
}
33293356

‎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