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

Commit27ff4cf

Browse files
committed
Disallow LATERAL references to the target table of an UPDATE/DELETE.
On second thought, commit0c051c9 wasover-hasty: rather than allowing this case, we ought to reject it for now.That leaves the field clear for a future feature that allows the targettable to be re-specified in the FROM (or USING) clause, which will enableleft-joining the target table to something else. We can then also allowLATERAL references to such an explicitly re-specified target table.But allowing them right now will create ambiguities or worse for such afeature, and it isn't something we documented 9.3 as supporting.While at it, add a convenience subroutine to avoid having several copiesof the ereport for disalllowed-LATERAL-reference cases.
1 parent5bfcc9e commit27ff4cf

File tree

5 files changed

+72
-66
lines changed

5 files changed

+72
-66
lines changed

‎src/backend/parser/analyze.c

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -367,8 +367,9 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
367367
/* there's no DISTINCT in DELETE */
368368
qry->distinctClause=NIL;
369369

370-
/* subqueries in USINGcan see the result relation only via LATERAL */
370+
/* subqueries in USINGcannot access the result relation */
371371
nsitem->p_lateral_only= true;
372+
nsitem->p_lateral_ok= false;
372373

373374
/*
374375
* The USING clause is non-standard SQL syntax, and is equivalent in
@@ -378,8 +379,9 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
378379
*/
379380
transformFromClause(pstate,stmt->usingClause);
380381

381-
/* remaining clauses cansee the result relation normally */
382+
/* remaining clauses canreference the result relation normally */
382383
nsitem->p_lateral_only= false;
384+
nsitem->p_lateral_ok= true;
383385

384386
qual=transformWhereClause(pstate,stmt->whereClause,
385387
EXPR_KIND_WHERE,"WHERE");
@@ -1925,17 +1927,19 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
19251927
/* grab the namespace item made by setTargetTable */
19261928
nsitem= (ParseNamespaceItem*)llast(pstate->p_namespace);
19271929

1928-
/* subqueries in FROMcan see the result relation only via LATERAL */
1930+
/* subqueries in FROMcannot access the result relation */
19291931
nsitem->p_lateral_only= true;
1932+
nsitem->p_lateral_ok= false;
19301933

19311934
/*
19321935
* the FROM clause is non-standard SQL syntax. We used to be able to do
19331936
* this with REPLACE in POSTQUEL so we keep the feature.
19341937
*/
19351938
transformFromClause(pstate,stmt->fromClause);
19361939

1937-
/* remaining clauses cansee the result relation normally */
1940+
/* remaining clauses canreference the result relation normally */
19381941
nsitem->p_lateral_only= false;
1942+
nsitem->p_lateral_ok= true;
19391943

19401944
qry->targetList=transformTargetList(pstate,stmt->targetList,
19411945
EXPR_KIND_UPDATE_SOURCE);

‎src/backend/parser/parse_relation.c

Lines changed: 37 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,8 @@ static RangeTblEntry *scanNameSpaceForRefname(ParseState *pstate,
3838
constchar*refname,intlocation);
3939
staticRangeTblEntry*scanNameSpaceForRelid(ParseState*pstate,Oidrelid,
4040
intlocation);
41+
staticvoidcheck_lateral_ref_ok(ParseState*pstate,ParseNamespaceItem*nsitem,
42+
intlocation);
4143
staticvoidmarkRTEForSelectPriv(ParseState*pstate,RangeTblEntry*rte,
4244
intrtindex,AttrNumbercol);
4345
staticvoidexpandRelation(Oidrelid,Alias*eref,
@@ -170,14 +172,7 @@ scanNameSpaceForRefname(ParseState *pstate, const char *refname, int location)
170172
errmsg("table reference \"%s\" is ambiguous",
171173
refname),
172174
parser_errposition(pstate,location)));
173-
/* SQL:2008 demands this be an error, not an invisible item */
174-
if (nsitem->p_lateral_only&& !nsitem->p_lateral_ok)
175-
ereport(ERROR,
176-
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
177-
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
178-
refname),
179-
errdetail("The combining JOIN type must be INNER or LEFT for a LATERAL reference."),
180-
parser_errposition(pstate,location)));
175+
check_lateral_ref_ok(pstate,nsitem,location);
181176
result=rte;
182177
}
183178
}
@@ -221,14 +216,7 @@ scanNameSpaceForRelid(ParseState *pstate, Oid relid, int location)
221216
errmsg("table reference %u is ambiguous",
222217
relid),
223218
parser_errposition(pstate,location)));
224-
/* SQL:2008 demands this be an error, not an invisible item */
225-
if (nsitem->p_lateral_only&& !nsitem->p_lateral_ok)
226-
ereport(ERROR,
227-
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
228-
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
229-
rte->eref->aliasname),
230-
errdetail("The combining JOIN type must be INNER or LEFT for a LATERAL reference."),
231-
parser_errposition(pstate,location)));
219+
check_lateral_ref_ok(pstate,nsitem,location);
232220
result=rte;
233221
}
234222
}
@@ -410,6 +398,37 @@ checkNameSpaceConflicts(ParseState *pstate, List *namespace1,
410398
}
411399
}
412400

401+
/*
402+
* Complain if a namespace item is currently disallowed as a LATERAL reference.
403+
* This enforces both SQL:2008's rather odd idea of what to do with a LATERAL
404+
* reference to the wrong side of an outer join, and our own prohibition on
405+
* referencing the target table of an UPDATE or DELETE as a lateral reference
406+
* in a FROM/USING clause.
407+
*
408+
* Convenience subroutine to avoid multiple copies of a rather ugly ereport.
409+
*/
410+
staticvoid
411+
check_lateral_ref_ok(ParseState*pstate,ParseNamespaceItem*nsitem,
412+
intlocation)
413+
{
414+
if (nsitem->p_lateral_only&& !nsitem->p_lateral_ok)
415+
{
416+
/* SQL:2008 demands this be an error, not an invisible item */
417+
RangeTblEntry*rte=nsitem->p_rte;
418+
char*refname=rte->eref->aliasname;
419+
420+
ereport(ERROR,
421+
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
422+
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
423+
refname),
424+
(rte==pstate->p_target_rangetblentry) ?
425+
errhint("There is an entry for table \"%s\", but it cannot be referenced from this part of the query.",
426+
refname) :
427+
errdetail("The combining JOIN type must be INNER or LEFT for a LATERAL reference."),
428+
parser_errposition(pstate,location)));
429+
}
430+
}
431+
413432
/*
414433
* given an RTE, return RT index (starting with 1) of the entry,
415434
* and optionally get its nesting depth (0 = current).If sublevels_up
@@ -622,15 +641,8 @@ colNameToVar(ParseState *pstate, char *colname, bool localonly,
622641
(errcode(ERRCODE_AMBIGUOUS_COLUMN),
623642
errmsg("column reference \"%s\" is ambiguous",
624643
colname),
625-
parser_errposition(orig_pstate,location)));
626-
/* SQL:2008 demands this be an error, not an invisible item */
627-
if (nsitem->p_lateral_only&& !nsitem->p_lateral_ok)
628-
ereport(ERROR,
629-
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
630-
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
631-
rte->eref->aliasname),
632-
errdetail("The combining JOIN type must be INNER or LEFT for a LATERAL reference."),
633-
parser_errposition(orig_pstate,location)));
644+
parser_errposition(pstate,location)));
645+
check_lateral_ref_ok(pstate,nsitem,location);
634646
result=newresult;
635647
}
636648
}

‎src/include/parser/parse_node.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -185,7 +185,8 @@ struct ParseState
185185
* inside such a subexpression at the moment.)If p_lateral_ok is not set,
186186
* it's an error to actually use such a namespace item. One might think it
187187
* would be better to just exclude such items from visibility, but the wording
188-
* of SQL:2008 requires us to do it this way.
188+
* of SQL:2008 requires us to do it this way. We also use p_lateral_ok to
189+
* forbid LATERAL references to an UPDATE/DELETE target table.
189190
*
190191
* At no time should a namespace list contain two entries that conflict
191192
* according to the rules in checkNameSpaceConflicts; but note that those

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

Lines changed: 19 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -4057,17 +4057,7 @@ LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
40574057
^
40584058
-- check behavior of LATERAL in UPDATE/DELETE
40594059
create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
4060-
select * from xx1;
4061-
x1 | x2
4062-
-------------+-------------
4063-
0 | 0
4064-
123456 | -123456
4065-
-123456 | 123456
4066-
2147483647 | -2147483647
4067-
-2147483647 | 2147483647
4068-
(5 rows)
4069-
4070-
-- error, can't do this without LATERAL:
4060+
-- error, can't do this:
40714061
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
40724062
ERROR: column "x1" does not exist
40734063
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
@@ -4078,28 +4068,28 @@ ERROR: invalid reference to FROM-clause entry for table "xx1"
40784068
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
40794069
^
40804070
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
4081-
--OK:
4071+
--can't do it even with LATERAL:
40824072
update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
4083-
select * from xx1;
4084-
x1 | x2
4085-
-------------+-------------
4086-
0 | 0
4087-
123456 | 123456
4088-
-123456 | -123456
4089-
2147483647 | 2147483647
4090-
-2147483647 | -2147483647
4091-
(5 rows)
4092-
4093-
-- error:
4073+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4074+
LINE 1: ...= f1 from lateral (select * from int4_tbl where f1 = x1) ss;
4075+
^
4076+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
4077+
-- we might in future allow something like this, but for now it's an error:
4078+
update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
4079+
ERROR: table name "xx1" specified more than once
4080+
-- also errors:
40944081
delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
40954082
ERROR: column "x1" does not exist
40964083
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
40974084
^
40984085
HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
4099-
-- OK:
4086+
delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
4087+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4088+
LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
4089+
^
4090+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
41004091
delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
4101-
select * from xx1;
4102-
x1 | x2
4103-
----+----
4104-
(0 rows)
4105-
4092+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4093+
LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
4094+
^
4095+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.

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

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1137,17 +1137,16 @@ select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
11371137
-- check behavior of LATERAL in UPDATE/DELETE
11381138

11391139
create temp table xx1asselect f1as x1,-f1as x2from int4_tbl;
1140-
select*from xx1;
11411140

1142-
-- error, can't do this without LATERAL:
1141+
-- error, can't do this:
11431142
update xx1set x2= f1from (select*from int4_tblwhere f1= x1) ss;
11441143
update xx1set x2= f1from (select*from int4_tblwhere f1=xx1.x1) ss;
1145-
--OK:
1144+
--can't do it even with LATERAL:
11461145
update xx1set x2= f1from lateral (select*from int4_tblwhere f1= x1) ss;
1147-
select*from xx1;
1146+
-- we might in future allow something like this, but for now it's an error:
1147+
update xx1set x2= f1from xx1, lateral (select*from int4_tblwhere f1= x1) ss;
11481148

1149-
--error:
1149+
--also errors:
11501150
deletefrom xx1 using (select*from int4_tblwhere f1= x1) ss;
1151-
-- OK:
1151+
deletefrom xx1 using (select*from int4_tblwhere f1=xx1.x1) ss;
11521152
deletefrom xx1 using lateral (select*from int4_tblwhere f1= x1) ss;
1153-
select*from xx1;

0 commit comments

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