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

Commit158b7fa

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 parent910bac5 commit158b7fa

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
@@ -37,6 +37,8 @@ static RangeTblEntry *scanNameSpaceForRefname(ParseState *pstate,
3737
constchar*refname,intlocation);
3838
staticRangeTblEntry*scanNameSpaceForRelid(ParseState*pstate,Oidrelid,
3939
intlocation);
40+
staticvoidcheck_lateral_ref_ok(ParseState*pstate,ParseNamespaceItem*nsitem,
41+
intlocation);
4042
staticvoidmarkRTEForSelectPriv(ParseState*pstate,RangeTblEntry*rte,
4143
intrtindex,AttrNumbercol);
4244
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
@@ -632,15 +651,8 @@ colNameToVar(ParseState *pstate, char *colname, bool localonly,
632651
(errcode(ERRCODE_AMBIGUOUS_COLUMN),
633652
errmsg("column reference \"%s\" is ambiguous",
634653
colname),
635-
parser_errposition(orig_pstate,location)));
636-
/* SQL:2008 demands this be an error, not an invisible item */
637-
if (nsitem->p_lateral_only&& !nsitem->p_lateral_ok)
638-
ereport(ERROR,
639-
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
640-
errmsg("invalid reference to FROM-clause entry for table \"%s\"",
641-
rte->eref->aliasname),
642-
errdetail("The combining JOIN type must be INNER or LEFT for a LATERAL reference."),
643-
parser_errposition(orig_pstate,location)));
654+
parser_errposition(pstate,location)));
655+
check_lateral_ref_ok(pstate,nsitem,location);
644656
result=newresult;
645657
}
646658
}

‎src/include/parser/parse_node.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -186,7 +186,8 @@ struct ParseState
186186
* inside such a subexpression at the moment.)If p_lateral_ok is not set,
187187
* it's an error to actually use such a namespace item. One might think it
188188
* would be better to just exclude such items from visibility, but the wording
189-
* of SQL:2008 requires us to do it this way.
189+
* of SQL:2008 requires us to do it this way. We also use p_lateral_ok to
190+
* forbid LATERAL references to an UPDATE/DELETE target table.
190191
*
191192
* At no time should a namespace list contain two entries that conflict
192193
* 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
@@ -4105,17 +4105,7 @@ LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
41054105
^
41064106
-- check behavior of LATERAL in UPDATE/DELETE
41074107
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:
4108+
-- error, can't do this:
41194109
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
41204110
ERROR: column "x1" does not exist
41214111
LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
@@ -4126,28 +4116,28 @@ ERROR: invalid reference to FROM-clause entry for table "xx1"
41264116
LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
41274117
^
41284118
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
4129-
--OK:
4119+
--can't do it even with LATERAL:
41304120
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:
4121+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4122+
LINE 1: ...= f1 from lateral (select * from int4_tbl where f1 = x1) ss;
4123+
^
4124+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
4125+
-- we might in future allow something like this, but for now it's an error:
4126+
update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
4127+
ERROR: table name "xx1" specified more than once
4128+
-- also errors:
41424129
delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
41434130
ERROR: column "x1" does not exist
41444131
LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
41454132
^
41464133
HINT: There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
4147-
-- OK:
4134+
delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
4135+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4136+
LINE 1: ...from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss...
4137+
^
4138+
HINT: There is an entry for table "xx1", but it cannot be referenced from this part of the query.
41484139
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-
4140+
ERROR: invalid reference to FROM-clause entry for table "xx1"
4141+
LINE 1: ...xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
4142+
^
4143+
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
@@ -1151,17 +1151,16 @@ select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
11511151
-- check behavior of LATERAL in UPDATE/DELETE
11521152

11531153
create temp table xx1asselect f1as x1,-f1as x2from int4_tbl;
1154-
select*from xx1;
11551154

1156-
-- error, can't do this without LATERAL:
1155+
-- error, can't do this:
11571156
update xx1set x2= f1from (select*from int4_tblwhere f1= x1) ss;
11581157
update xx1set x2= f1from (select*from int4_tblwhere f1=xx1.x1) ss;
1159-
--OK:
1158+
--can't do it even with LATERAL:
11601159
update xx1set x2= f1from lateral (select*from int4_tblwhere f1= x1) ss;
1161-
select*from xx1;
1160+
-- we might in future allow something like this, but for now it's an error:
1161+
update xx1set x2= f1from xx1, lateral (select*from int4_tblwhere f1= x1) ss;
11621162

1163-
--error:
1163+
--also errors:
11641164
deletefrom xx1 using (select*from int4_tblwhere f1= x1) ss;
1165-
-- OK:
1165+
deletefrom xx1 using (select*from int4_tblwhere f1=xx1.x1) ss;
11661166
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