Skip to content

Commit 27ff4cf

Browse files
tglsfdctglsfdc
committed
Disallow LATERAL references to the target table of an UPDATE/DELETE.
On second thought, commit 0c051c9 was over-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 target table to be re-specified in the FROM (or USING) clause, which will enable left-joining the target table to something else. We can then also allow LATERAL references to such an explicitly re-specified target table. But allowing them right now will create ambiguities or worse for such a feature, and it isn't something we documented 9.3 as supporting. While at it, add a convenience subroutine to avoid having several copies of the ereport for disalllowed-LATERAL-reference cases.
1 parent 5bfcc9e commit 27ff4cf

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 USING can see the result relation only via LATERAL */
370+
/* subqueries in USING cannot 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 can see the result relation normally */
382+
/* remaining clauses can reference 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 FROM can see the result relation only via LATERAL */
1930+
/* subqueries in FROM cannot 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 can see the result relation normally */
1940+
/* remaining clauses can reference 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
const char *refname, int location);
3939
static RangeTblEntry *scanNameSpaceForRelid(ParseState *pstate, Oid relid,
4040
int location);
41+
static void check_lateral_ref_ok(ParseState *pstate, ParseNamespaceItem *nsitem,
42+
int location);
4143
static void markRTEForSelectPriv(ParseState *pstate, RangeTblEntry *rte,
4244
int rtindex, AttrNumber col);
4345
static void expandRelation(Oid relid, 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+
static void
411+
check_lateral_ref_ok(ParseState *pstate, ParseNamespaceItem *nsitem,
412+
int location)
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 xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
1140-
select * from xx1;
11411140

1142-
-- error, can't do this without LATERAL:
1141+
-- error, can't do this:
11431142
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
11441143
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
1145-
-- OK:
1144+
-- can't do it even with LATERAL:
11461145
update xx1 set x2 = f1 from lateral (select * from int4_tbl where 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 xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
11481148

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

0 commit comments

Comments
 (0)