On 18/2/2024 23:18, Alexander Korotkov wrote:
On Sun, Feb 18, 2024 at 5:04 PM Alexander Korotkov <aekorot...@gmail.com> wrote:
On Sun, Feb 18, 2024 at 3:00 PM Alexander Lakhin <exclus...@gmail.com> wrote:
09.01.2024 01:09, Alexander Korotkov wrote:

Fixed in 30b4955a46.


Please look at the following query which fails with an error since
d3d55ce57:

create table t (i int primary key);

select t3.i from t t1
  join t t2 on t1.i = t2.i,
  lateral (select t1.i limit 1) t3;

ERROR:  non-LATERAL parameter required by subquery

Thank you for spotting.  I'm looking at this.

Attached is a draft patch fixing this query.  Could you, please, recheck?
I reviewed this patch. Why do you check only the target list? I guess these links can be everywhere. See the patch in the attachment with the elaborated test and slightly changed code.

--
regards,
Andrei Lepikhov
Postgres Professional
From 7f94a3c96fd410522b87e570240cdb96b300dd31 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Mon, 19 Feb 2024 12:17:55 +0700
Subject: [PATCH] Replace relids in lateral subquery target list during SJE

---
 src/backend/optimizer/plan/analyzejoins.c | 29 ++++++++++++++-
 src/test/regress/expected/join.out        | 44 +++++++++++++++++++++++
 src/test/regress/sql/join.sql             | 12 +++++++
 3 files changed, 84 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/plan/analyzejoins.c 
b/src/backend/optimizer/plan/analyzejoins.c
index e494acd51a..072298f66c 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -395,7 +395,34 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
                }
 
                /* Update lateral references. */
-               replace_varno((Node *) otherrel->lateral_vars, relid, subst);
+               if (root->hasLateralRTEs)
+               {
+                       RangeTblEntry *rte = root->simple_rte_array[rti];
+                       ReplaceVarnoContext ctx = {.from = relid,.to = subst};
+
+                       if (rte->lateral)
+                       {
+                               replace_varno((Node *) otherrel->lateral_vars, 
relid, subst);
+
+                               /*
+                                * Although we pass root->parse through cleanup 
procedure,
+                                * but parse->rtable and rte contains refs to 
different copies
+                                * of the subquery.
+                                */
+                               if (otherrel->rtekind == RTE_SUBQUERY)
+                                       query_tree_walker(rte->subquery, 
replace_varno_walker, &ctx,
+                                                                         
QTW_EXAMINE_SORTGROUP);
+#ifdef USE_ASSERT_CHECKING
+                               /* Just check possibly hidden non-replaced 
relids */
+                               Assert(!bms_is_member(relid, pull_varnos(root, 
(Node *) rte->tablesample)));
+                               Assert(!bms_is_member(relid, pull_varnos(root, 
(Node *) rte->functions)));
+                               Assert(!bms_is_member(relid, pull_varnos(root, 
(Node *) rte->tablefunc)));
+                               Assert(!bms_is_member(relid, pull_varnos(root, 
(Node *) rte->values_lists)));
+#endif
+                       }
+               }
+
+
        }
 
        /*
diff --git a/src/test/regress/expected/join.out 
b/src/test/regress/expected/join.out
index 0c2cba8921..d560a4a6b9 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6349,6 +6349,50 @@ on true;
                ->  Seq Scan on int8_tbl y
 (7 rows)
 
+-- Test processing target lists in lateral subqueries
+explain (verbose, costs off)
+SELECT t3.a FROM sj t1, sj t2,
+LATERAL (SELECT t1.a WHERE t1.a <> 1
+                GROUP BY (t1.a) HAVING t1.a > 0 ORDER BY t1.a LIMIT 1) t3,
+LATERAL (SELECT t1.a,t3.a WHERE t1.a <> t3.a+t2.a
+                GROUP BY (t3.a) HAVING t1.a > t3.a*t3.a+t2.a/t1.a LIMIT 2) t4,
+LATERAL (SELECT * FROM sj TABLESAMPLE bernoulli(t1.a/t2.a)
+                REPEATABLE (t1.a+t2.a)) t5,
+LATERAL generate_series(1, t1.a + t2.a) AS t6
+WHERE t1.a = t2.a;
+                                                          QUERY PLAN           
                                                
+-------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Output: (t2.a)
+   ->  Nested Loop
+         Output: t2.a, (t2.a)
+         ->  Nested Loop
+               Output: t2.a, (t2.a)
+               ->  Nested Loop
+                     Output: t2.a, (t2.a)
+                     ->  Seq Scan on public.sj t2
+                           Output: t2.a, t2.b, t2.c
+                           Filter: (t2.a IS NOT NULL)
+                     ->  Limit
+                           Output: (t2.a)
+                           ->  Group
+                                 Output: t2.a
+                                 ->  Result
+                                       One-Time Filter: ((t2.a <> 1) AND (t2.a 
> 0))
+               ->  Limit
+                     Output: NULL::integer, ((t2.a))
+                     ->  Group
+                           Output: NULL::integer, (t2.a)
+                           ->  Result
+                                 One-Time Filter: ((t2.a <> ((t2.a) + t2.a)) 
AND (t2.a > (((t2.a) * (t2.a)) + (t2.a / t2.a))))
+         ->  Sample Scan on public.sj
+               Output: sj.a, sj.b, sj.c
+               Sampling: bernoulli ((t2.a / t2.a)) REPEATABLE ((t2.a + t2.a))
+   ->  Function Scan on pg_catalog.generate_series t6
+         Output: t6.t6
+         Function Call: generate_series(1, (t2.a + t2.a))
+(29 rows)
+
 -- Check updating of Lateral links from top-level query to the removing 
relation
 explain (COSTS OFF)
 SELECT * FROM pg_am am WHERE am.amname IN (
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 257f727a2b..9341f80bbc 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2406,6 +2406,18 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
        on true) z
 on true;
 
+-- Test processing target lists in lateral subqueries
+explain (verbose, costs off)
+SELECT t3.a FROM sj t1, sj t2,
+LATERAL (SELECT t1.a WHERE t1.a <> 1
+                GROUP BY (t1.a) HAVING t1.a > 0 ORDER BY t1.a LIMIT 1) t3,
+LATERAL (SELECT t1.a,t3.a WHERE t1.a <> t3.a+t2.a
+                GROUP BY (t3.a) HAVING t1.a > t3.a*t3.a+t2.a/t1.a LIMIT 2) t4,
+LATERAL (SELECT * FROM sj TABLESAMPLE bernoulli(t1.a/t2.a)
+                REPEATABLE (t1.a+t2.a)) t5,
+LATERAL generate_series(1, t1.a + t2.a) AS t6
+WHERE t1.a = t2.a;
+
 -- Check updating of Lateral links from top-level query to the removing 
relation
 explain (COSTS OFF)
 SELECT * FROM pg_am am WHERE am.amname IN (
-- 
2.43.0

Reply via email to