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 or
USING clause (respectively) could access the update/delete target table as
though 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 a
suitable error when you omit LATERAL.  Per report from Emre Hasegeli.

2 files changed, 66 insertions, 0 deletions

diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 83cbde82c6a..9adfac5ef67 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4103,3 +4103,51 @@ select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
 ERROR:  aggregate functions are not allowed in FROM clause of their own query level
 LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
                                                ^
+-- check behavior of LATERAL in UPDATE/DELETE
+create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
+select * from xx1;
+     x1      |     x2      
+-------------+-------------
+           0 |           0
+      123456 |     -123456
+     -123456 |      123456
+  2147483647 | -2147483647
+ -2147483647 |  2147483647
+(5 rows)
+
+-- error, can't do this without LATERAL:
+update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
+ERROR:  column "x1" does not exist
+LINE 1: ... set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
+                                                                ^
+HINT:  There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
+ERROR:  invalid reference to FROM-clause entry for table "xx1"
+LINE 1: ...t x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss...
+                                                             ^
+HINT:  There is an entry for table "xx1", but it cannot be referenced from this part of the query.
+-- OK:
+update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
+select * from xx1;
+     x1      |     x2      
+-------------+-------------
+           0 |           0
+      123456 |      123456
+     -123456 |     -123456
+  2147483647 |  2147483647
+ -2147483647 | -2147483647
+(5 rows)
+
+-- error:
+delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
+ERROR:  column "x1" does not exist
+LINE 1: ...te from xx1 using (select * from int4_tbl where f1 = x1) ss;
+                                                                ^
+HINT:  There is a column named "x1" in table "xx1", but it cannot be referenced from this part of the query.
+-- OK:
+delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
+select * from xx1;
+ x1 | x2 
+----+----
+(0 rows)
+
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 2168c557227..e2bf915be06 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1147,3 +1147,21 @@ select * from
   int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss);
 -- LATERAL can be used to put an aggregate into the FROM clause of its query
 select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
+
+-- check behavior of LATERAL in UPDATE/DELETE
+
+create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
+select * from xx1;
+
+-- error, can't do this without LATERAL:
+update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
+update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
+-- OK:
+update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
+select * from xx1;
+
+-- error:
+delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
+-- OK:
+delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
+select * from xx1;