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

Commit0c051c9

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 parentf68220d commit0c051c9

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
@@ -204,6 +204,10 @@ setTargetTable(ParseState *pstate, RangeVar *relation,
204204

205205
/*
206206
* If UPDATE/DELETE, add table to joinlist and namespace.
207+
*
208+
* Note: some callers know that they can find the new ParseNamespaceItem
209+
* at the end of the pstate->p_namespace list. This is a bit ugly but not
210+
* worth complicating this function's signature for.
207211
*/
208212
if (alsoSource)
209213
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
@@ -1730,6 +1730,10 @@ isLockedRefname(ParseState *pstate, const char *refname)
17301730
* and/or namespace list. (We assume caller has checked for any
17311731
* namespace conflicts.) The RTE is always marked as unconditionally
17321732
* visible, that is, not LATERAL-only.
1733+
*
1734+
* Note: some callers know that they can find the new ParseNamespaceItem
1735+
* at the end of the pstate->p_namespace list. This is a bit ugly but not
1736+
* worth complicating this function's signature for.
17331737
*/
17341738
void
17351739
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
@@ -4103,3 +4103,51 @@ select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
41034103
ERROR: aggregate functions are not allowed in FROM clause of their own query level
41044104
LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
41054105
^
4106+
-- check behavior of LATERAL in UPDATE/DELETE
4107+
create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
4108+
select * from xx1;
4109+
x1 | x2
4110+
-------------+-------------
4111+
0 | 0
4112+
123456 | -123456
4113+
-123456 | 123456
4114+
2147483647 | -2147483647
4115+
-2147483647 | 2147483647
4116+
(5 rows)
4117+
4118+
-- error, can't do this without LATERAL:
4119+
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
4120+
ERROR: column "x1" does not exist
4121+
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
4122+
^
4123+
HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
4124+
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
4125+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4126+
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
4127+
^
4128+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
4129+
-- OK:
4130+
update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
4131+
select * from xx1;
4132+
x1 | x2
4133+
-------------+-------------
4134+
0 | 0
4135+
123456 | 123456
4136+
-123456 | -123456
4137+
2147483647 | 2147483647
4138+
-2147483647 | -2147483647
4139+
(5 rows)
4140+
4141+
-- error:
4142+
delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
4143+
ERROR: column "x1" does not exist
4144+
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
4145+
^
4146+
HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
4147+
-- OK:
4148+
delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
4149+
select * from xx1;
4150+
x1 | x2
4151+
----+----
4152+
(0 rows)
4153+

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

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1147,3 +1147,21 @@ select * from
11471147
int8_tbl xcross join (int4_tbl xcross join lateral (selectx.f1) ss);
11481148
-- LATERAL can be used to put an aggregate into the FROM clause of its query
11491149
select1from tenk1 a, lateral (selectmax(a.unique1)from int4_tbl b) ss;
1150+
1151+
-- check behavior of LATERAL in UPDATE/DELETE
1152+
1153+
create temp table xx1asselect f1as x1,-f1as x2from int4_tbl;
1154+
select*from xx1;
1155+
1156+
-- error, can't do this without LATERAL:
1157+
update xx1set x2= f1from (select*from int4_tblwhere f1= x1) ss;
1158+
update xx1set x2= f1from (select*from int4_tblwhere f1=xx1.x1) ss;
1159+
-- OK:
1160+
update xx1set x2= f1from lateral (select*from int4_tblwhere f1= x1) ss;
1161+
select*from xx1;
1162+
1163+
-- error:
1164+
deletefrom xx1 using (select*from int4_tblwhere f1= x1) ss;
1165+
-- OK:
1166+
deletefrom xx1 using lateral (select*from int4_tblwhere f1= x1) ss;
1167+
select*from xx1;

0 commit comments

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