pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Japin Li <japinli@hotmail.com>
Subject: [PATCH v4] Disable push down FETCH FIRST WITH TIES clause
Date: Thu, 16 May 2024 17:42:53 +0800
---
.../postgres_fdw/expected/postgres_fdw.out | 19 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 10 ++++++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 2 ++
3 files changed, 31 insertions(+)
inline patch 1: contrib/postgres_fdw/expected/postgres_fdw.out, contrib/postgres_fdw/postgres_fdw.c, contrib/postgres_fdw/sql/postgres_fdw.sql (+31/-0 lines) download inline-1.patch diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 9ae36d3059..f2b988da19 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -339,6 +339,25 @@ SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
(110,0,00110,"Sun Jan 11 00:00:00 1970 PST","Sun Jan 11 00:00:00 1970",0,"0 ",foo)
(10 rows)
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c3 > '00960' ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ -> Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 > '00960')) ORDER BY c2 ASC NULLS LAST
+(5 rows)
+
+SELECT * FROM ft1 t1 WHERE t1.c3 > '00960' ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+------+----+-------+------------------------------+--------------------------+----+------------+-----
+ 970 | 0 | 00970 | Thu Mar 12 00:00:00 1970 PST | Thu Mar 12 00:00:00 1970 | 0 | 0 | foo
+ 1000 | 0 | 01000 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0 | 0 | foo
+ 990 | 0 | 00990 | Wed Apr 01 00:00:00 1970 PST | Wed Apr 01 00:00:00 1970 | 0 | 0 | foo
+ 980 | 0 | 00980 | Sun Mar 22 00:00:00 1970 PST | Sun Mar 22 00:00:00 1970 | 0 | 0 | foo
+(4 rows)
+
-- empty result
SELECT * FROM ft1 WHERE false;
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 4053cd641c..4724de0638 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -7140,6 +7140,16 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel,
if (ifpinfo->local_conds)
return;
+ /*
+ * Also, the FETCH FIRST/NEXT ... ROW/ROWS WITH TIES cannot be pushed down
+ * due to the remote having:
+ * a) a different understanding of equality, resulting in varying results,
+ such as non-deterministic collations;
+ * b) potential lack of support for this clause.
+ */
+ if (parse->limitOption == LIMIT_OPTION_WITH_TIES)
+ return;
+
/*
* Also, the LIMIT/OFFSET cannot be pushed down, if their expressions are
* not safe to remote.
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 1f31ac14df..266d7b41cc 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -265,6 +265,8 @@ SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1, t1.tableoid OFFSET 100 LIMIT 10;
-- whole-row reference
EXPLAIN (VERBOSE, COSTS OFF) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c3 > '00960' ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
+SELECT * FROM ft1 t1 WHERE t1.c3 > '00960' ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES;
-- empty result
SELECT * FROM ft1 WHERE false;
-- with WHERE clause
--
2.41.0
Message-ID: <no-message-id-25543@localhost>
Permalink: ../no-message-id-25543@localhost/
Also on: postgresql.org/message-id/no-message-id-25543@localhost
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: japinli@hotmail.com
Subject: Re: [PATCH v4] Disable push down FETCH FIRST WITH TIES clause
In-Reply-To: <no-message-id-25543@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox