summaryrefslogtreecommitdiff
path: root/contrib/postgres_fdw
diff options
context:
space:
mode:
Diffstat (limited to 'contrib/postgres_fdw')
-rw-r--r--contrib/postgres_fdw/expected/postgres_fdw.out56
-rw-r--r--contrib/postgres_fdw/postgres_fdw.c18
-rw-r--r--contrib/postgres_fdw/sql/postgres_fdw.sql26
3 files changed, 96 insertions, 4 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 5d60ead91cc..cdd788f8257 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3435,6 +3435,62 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr
(2 rows)
reset enable_hashagg;
+-- bug #15613: bad plan for foreign table scan with lateral reference
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ref_0.c2, subq_1.*
+FROM
+ "S 1"."T 1" AS ref_0,
+ LATERAL (
+ SELECT ref_0."C 1" c1, subq_0.*
+ FROM (SELECT ref_0.c2, ref_1.c3
+ FROM ft1 AS ref_1) AS subq_0
+ RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3)
+ ) AS subq_1
+WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001'
+ORDER BY ref_0."C 1";
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: ref_0.c2, ref_0."C 1", (ref_0.c2), ref_1.c3, ref_0."C 1"
+ -> Nested Loop
+ Output: ref_0.c2, ref_0."C 1", ref_1.c3, (ref_0.c2)
+ -> Index Scan using t1_pkey on "S 1"."T 1" ref_0
+ Output: ref_0."C 1", ref_0.c2, ref_0.c3, ref_0.c4, ref_0.c5, ref_0.c6, ref_0.c7, ref_0.c8
+ Index Cond: (ref_0."C 1" < 10)
+ -> Foreign Scan on public.ft1 ref_1
+ Output: ref_1.c3, ref_0.c2
+ Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 = '00001'::text))
+ -> Materialize
+ Output: ref_3.c3
+ -> Foreign Scan on public.ft2 ref_3
+ Output: ref_3.c3
+ Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE ((c3 = '00001'::text))
+(15 rows)
+
+SELECT ref_0.c2, subq_1.*
+FROM
+ "S 1"."T 1" AS ref_0,
+ LATERAL (
+ SELECT ref_0."C 1" c1, subq_0.*
+ FROM (SELECT ref_0.c2, ref_1.c3
+ FROM ft1 AS ref_1) AS subq_0
+ RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3)
+ ) AS subq_1
+WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001'
+ORDER BY ref_0."C 1";
+ c2 | c1 | c2 | c3
+----+----+----+-------
+ 1 | 1 | 1 | 00001
+ 2 | 2 | 2 | 00001
+ 3 | 3 | 3 | 00001
+ 4 | 4 | 4 | 00001
+ 5 | 5 | 5 | 00001
+ 6 | 6 | 6 | 00001
+ 7 | 7 | 7 | 00001
+ 8 | 8 | 8 | 00001
+ 9 | 9 | 9 | 00001
+(9 rows)
+
-- Check with placeHolderVars
explain (verbose, costs off)
select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b);
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index f0be58f60c7..fea288e8ebb 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -930,6 +930,9 @@ postgresGetForeignPaths(PlannerInfo *root,
* baserestrict conditions we were able to send to remote, there might
* actually be an indexscan happening there). We already did all the work
* to estimate cost and size of this path.
+ *
+ * Although this path uses no join clauses, it could still have required
+ * parameterization due to LATERAL refs in its tlist.
*/
path = create_foreignscan_path(root, baserel,
NULL, /* default pathtarget */
@@ -937,7 +940,7 @@ postgresGetForeignPaths(PlannerInfo *root,
fpinfo->startup_cost,
fpinfo->total_cost,
NIL, /* no pathkeys */
- NULL, /* no outer rel either */
+ baserel->lateral_relids,
NULL, /* no extra plan */
NIL); /* no fdw_private list */
add_path(baserel, (Path *) path);
@@ -4978,7 +4981,7 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
startup_cost,
total_cost,
useful_pathkeys,
- NULL,
+ rel->lateral_relids,
sorted_epq_path,
NIL));
}
@@ -5116,6 +5119,13 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
return;
/*
+ * This code does not work for joins with lateral references, since those
+ * must have parameterized paths, which we don't generate yet.
+ */
+ if (!bms_is_empty(joinrel->lateral_relids))
+ return;
+
+ /*
* Create unfinished PgFdwRelationInfo entry which is used to indicate
* that the join relation is already considered, so that we won't waste
* time in judging safety of join pushdown and adding the same paths again
@@ -5206,7 +5216,7 @@ postgresGetForeignJoinPaths(PlannerInfo *root,
startup_cost,
total_cost,
NIL, /* no pathkeys */
- NULL, /* no required_outer */
+ joinrel->lateral_relids,
epq_path,
NIL); /* no fdw_private */
@@ -5534,7 +5544,7 @@ add_foreign_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
startup_cost,
total_cost,
NIL, /* no pathkeys */
- NULL, /* no required_outer */
+ grouped_rel->lateral_relids,
NULL,
NIL); /* no fdw_private */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index cef42d26f19..813286bba5f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -884,6 +884,32 @@ select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum fr
select c2, sum from "S 1"."T 1" t1, lateral (select sum(t2.c1 + t1."C 1") sum from ft2 t2 group by t2.c1) qry where t1.c2 * 2 = qry.sum and t1.c2 < 3 and t1."C 1" < 100 order by 1;
reset enable_hashagg;
+-- bug #15613: bad plan for foreign table scan with lateral reference
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ref_0.c2, subq_1.*
+FROM
+ "S 1"."T 1" AS ref_0,
+ LATERAL (
+ SELECT ref_0."C 1" c1, subq_0.*
+ FROM (SELECT ref_0.c2, ref_1.c3
+ FROM ft1 AS ref_1) AS subq_0
+ RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3)
+ ) AS subq_1
+WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001'
+ORDER BY ref_0."C 1";
+
+SELECT ref_0.c2, subq_1.*
+FROM
+ "S 1"."T 1" AS ref_0,
+ LATERAL (
+ SELECT ref_0."C 1" c1, subq_0.*
+ FROM (SELECT ref_0.c2, ref_1.c3
+ FROM ft1 AS ref_1) AS subq_0
+ RIGHT JOIN ft2 AS ref_3 ON (subq_0.c3 = ref_3.c3)
+ ) AS subq_1
+WHERE ref_0."C 1" < 10 AND subq_1.c3 = '00001'
+ORDER BY ref_0."C 1";
+
-- Check with placeHolderVars
explain (verbose, costs off)
select sum(q.a), count(q.b) from ft4 left join (select 13, avg(ft1.c1), sum(ft2.c1) from ft1 right join ft2 on (ft1.c1 = ft2.c1)) q(a, b, c) on (ft4.c1 <= q.b);
generated by cgit v1.2.3 (git 2.46.0) at 2025-09-08 00:08:18 +0000