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

Commit0a43e54

Browse files
committed
Fix LATERAL references to target table of UPDATE/DELETE.
I failed to think much about UPDATE/DELETE when implementing LATERAL :-(.The implemented behavior ended up being that subqueries in the FROM orUSING clause (respectively) could access the update/delete target table asthough it were a lateral reference; which seems fine if they said LATERAL,but certainly ought to draw an error if they didn't. Fix it so you get asuitable error when you omit LATERAL. Per report from Emre Hasegeli.
1 parent91c2755 commit0a43e54

File tree

5 files changed

+95
-0
lines changed

5 files changed

+95
-0
lines changed

‎src/backend/parser/analyze.c

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -342,6 +342,7 @@ static Query *
342342
transformDeleteStmt(ParseState*pstate,DeleteStmt*stmt)
343343
{
344344
Query*qry=makeNode(Query);
345+
ParseNamespaceItem*nsitem;
345346
Node*qual;
346347

347348
qry->commandType=CMD_DELETE;
@@ -360,8 +361,15 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
360361
true,
361362
ACL_DELETE);
362363

364+
/* grab the namespace item made by setTargetTable */
365+
nsitem= (ParseNamespaceItem*)llast(pstate->p_namespace);
366+
367+
/* there's no DISTINCT in DELETE */
363368
qry->distinctClause=NIL;
364369

370+
/* subqueries in USING can see the result relation only via LATERAL */
371+
nsitem->p_lateral_only= true;
372+
365373
/*
366374
* The USING clause is non-standard SQL syntax, and is equivalent in
367375
* functionality to the FROM list that can be specified for UPDATE. The
@@ -370,6 +378,9 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
370378
*/
371379
transformFromClause(pstate,stmt->usingClause);
372380

381+
/* remaining clauses can see the result relation normally */
382+
nsitem->p_lateral_only= false;
383+
373384
qual=transformWhereClause(pstate,stmt->whereClause,
374385
EXPR_KIND_WHERE,"WHERE");
375386

@@ -1889,6 +1900,7 @@ static Query *
18891900
transformUpdateStmt(ParseState*pstate,UpdateStmt*stmt)
18901901
{
18911902
Query*qry=makeNode(Query);
1903+
ParseNamespaceItem*nsitem;
18921904
RangeTblEntry*target_rte;
18931905
Node*qual;
18941906
ListCell*origTargetList;
@@ -1910,12 +1922,21 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
19101922
true,
19111923
ACL_UPDATE);
19121924

1925+
/* grab the namespace item made by setTargetTable */
1926+
nsitem= (ParseNamespaceItem*)llast(pstate->p_namespace);
1927+
1928+
/* subqueries in FROM can see the result relation only via LATERAL */
1929+
nsitem->p_lateral_only= true;
1930+
19131931
/*
19141932
* the FROM clause is non-standard SQL syntax. We used to be able to do
19151933
* this with REPLACE in POSTQUEL so we keep the feature.
19161934
*/
19171935
transformFromClause(pstate,stmt->fromClause);
19181936

1937+
/* remaining clauses can see the result relation normally */
1938+
nsitem->p_lateral_only= false;
1939+
19191940
qry->targetList=transformTargetList(pstate,stmt->targetList,
19201941
EXPR_KIND_UPDATE_SOURCE);
19211942

‎src/backend/parser/parse_clause.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -207,6 +207,10 @@ setTargetTable(ParseState *pstate, RangeVar *relation,
207207

208208
/*
209209
* If UPDATE/DELETE, add table to joinlist and namespace.
210+
*
211+
* Note: some callers know that they can find the new ParseNamespaceItem
212+
* at the end of the pstate->p_namespace list. This is a bit ugly but not
213+
* worth complicating this function's signature for.
210214
*/
211215
if (alsoSource)
212216
addRTEtoQuery(pstate,rte, true, true, true);

‎src/backend/parser/parse_relation.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1611,6 +1611,10 @@ isLockedRefname(ParseState *pstate, const char *refname)
16111611
* and/or namespace list. (We assume caller has checked for any
16121612
* namespace conflicts.) The RTE is always marked as unconditionally
16131613
* visible, that is, not LATERAL-only.
1614+
*
1615+
* Note: some callers know that they can find the new ParseNamespaceItem
1616+
* at the end of the pstate->p_namespace list. This is a bit ugly but not
1617+
* worth complicating this function's signature for.
16141618
*/
16151619
void
16161620
addRTEtoQuery(ParseState*pstate,RangeTblEntry*rte,

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

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4055,3 +4055,51 @@ select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
40554055
ERROR: aggregate functions are not allowed in FROM clause of their own query level
40564056
LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
40574057
^
4058+
-- check behavior of LATERAL in UPDATE/DELETE
4059+
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:
4071+
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
4072+
ERROR: column "x1" does not exist
4073+
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
4074+
^
4075+
HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
4076+
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
4077+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4078+
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
4079+
^
4080+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
4081+
-- OK:
4082+
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:
4094+
delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
4095+
ERROR: column "x1" does not exist
4096+
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
4097+
^
4098+
HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
4099+
-- OK:
4100+
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+

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1133,3 +1133,21 @@ select * from
11331133
int8_tbl xcross join (int4_tbl xcross join lateral (selectx.f1) ss);
11341134
-- LATERAL can be used to put an aggregate into the FROM clause of its query
11351135
select1from tenk1 a, lateral (selectmax(a.unique1)from int4_tbl b) ss;
1136+
1137+
-- check behavior of LATERAL in UPDATE/DELETE
1138+
1139+
create temp table xx1asselect f1as x1,-f1as x2from int4_tbl;
1140+
select*from xx1;
1141+
1142+
-- error, can't do this without LATERAL:
1143+
update xx1set x2= f1from (select*from int4_tblwhere f1= x1) ss;
1144+
update xx1set x2= f1from (select*from int4_tblwhere f1=xx1.x1) ss;
1145+
-- OK:
1146+
update xx1set x2= f1from lateral (select*from int4_tblwhere f1= x1) ss;
1147+
select*from xx1;
1148+
1149+
-- error:
1150+
deletefrom xx1 using (select*from int4_tblwhere f1= x1) ss;
1151+
-- OK:
1152+
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