Alexander Korotkov писал(а) 2025-03-18 03:27:
Hi, Robins!
On Tue, Mar 18, 2025 at 2:20 AM Robins Tharakan <thara...@gmail.com>
wrote:
On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <aekorot...@gmail.com>
wrote:
>
>
> Now, I think this looks good. I'm going to push this if no objections.
After this commit, I began seeing an unexpected ERROR - see this
bug-report.
https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
Thank you for pointing.
I'll check this in the next couple of days.
Hi.
It seems conditions, coming from semi-joins, are handled incorrectly
under left and right join. When deparsing left/right joins and there are
semi-joins in inner or outer part of the query, the corresponding part
is deparsed as subquery. And we can't refer subquery vars from above, so
just should not pull up the restrictinfos. The attached patch does
exactly this.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 04c89be4b2802a0b51b10ca4828108a338c77e71 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Tue, 18 Mar 2025 13:46:08 +0300
Subject: [PATCH] Avoid pulling up restrict infos from subqueries
Semi-join joins below left/right join are deparsed as
subqueries. We can't refer to subqueries vars from upper relations,
so avoid pulling conditions from them.
---
.../postgres_fdw/expected/postgres_fdw.out | 39 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 22 +++++++++--
contrib/postgres_fdw/sql/postgres_fdw.sql | 18 +++++++++
3 files changed, 75 insertions(+), 4 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bb4ed3059c4..88385e06f31 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4936,6 +4936,45 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
----+----+----+----+----+----+----+----+----+----+----
(0 rows)
+-- semi-join conditions shouldn't pop up as
+-- left/right join join clause
+set enable_material to off;
+EXPLAIN (verbose, costs off)
+ SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: NULL::text
+ Relations: ((public.ft2 ft2_1) SEMI JOIN (public.ft4 ft4_1)) LEFT JOIN ((public.ft2) SEMI JOIN (public.ft4))
+ Remote SQL: SELECT NULL FROM ((SELECT NULL FROM "S 1"."T 1" r6 WHERE ((r6."C 1" < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r7)) s8 LEFT JOIN (SELECT NULL FROM "S 1"."T 1" r4 WHERE ((r4."C 1" < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r5)) s9 ON (TRUE)) LIMIT 10::bigint
+(4 rows)
+
+SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+ ?column?
+----------
+
+
+
+
+
+
+
+
+
+
+(10 rows)
+
+reset enable_material;
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 1131a8bf77e..8af8b802a24 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5964,16 +5964,30 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_LEFT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in inner or outer part of left join,
+ * it's deparsed as a subquery and we can't refer to its vars
+ * on upper level
+ */
+ if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_i->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (bms_is_empty(fpinfo_o->hidden_subquery_rels))
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_o->remote_conds);
break;
case JOIN_RIGHT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in inner or outer part of right join,
+ * it's deparsed as a subquery and we can't refer to its vars
+ * on upper level
+ */
+ if (bms_is_empty(fpinfo_o->hidden_subquery_rels))
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_o->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_i->remote_conds);
break;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index d45e9f8ab52..faedeab9d41 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1455,6 +1455,24 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
WHERE ft2.c1 > 900
ORDER BY ft2.c1 LIMIT 10;
+-- semi-join conditions shouldn't pop up as
+-- left/right join join clause
+set enable_material to off;
+EXPLAIN (verbose, costs off)
+ SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+reset enable_material;
+
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
--
2.43.0
