diff options
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 21 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 14 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 5 |
3 files changed, 40 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index d7fdc6b8829..f105859aa0d 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1047,6 +1047,27 @@ SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; 1 | 1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo (1 row) +-- Ensure we don't ship FETCH FIRST .. WITH TIES +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES; + QUERY PLAN +------------------------------------------------------------------------------------------------- + Limit + Output: c2 + -> Foreign Scan on public.ft1 t1 + Output: c2 + Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE (("C 1" > 960)) ORDER BY c2 ASC NULLS LAST +(5 rows) + +SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES; + c2 +---- + 0 + 0 + 0 + 0 +(4 rows) + -- Test CASE pushdown EXPLAIN (VERBOSE, COSTS OFF) SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index e3b9b8d44d8..217d18232c6 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -6840,6 +6840,20 @@ add_foreign_final_paths(PlannerInfo *root, RelOptInfo *input_rel, return; /* + * If the query has FETCH FIRST .. WITH TIES, 1) it must have ORDER BY as + * well, which is used to determine which additional rows tie for the last + * place in the result set, and 2) ORDER BY must already have been + * determined to be safe to push down before we get here. So in that case + * the FETCH clause is safe to push down with ORDER BY if the remote + * server is v13 or later, but if not, the remote query will fail entirely + * for lack of support for it. Since we do not currently have a way to do + * a remote-version check (without accessing the remote server), disable + * pushing the FETCH clause for now. + */ + 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 fdbbb4fe3dd..2e5187a7797 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -407,6 +407,11 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2 order by t1.c2 limit 1; +-- Ensure we don't ship FETCH FIRST .. WITH TIES +EXPLAIN (VERBOSE, COSTS OFF) +SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES; +SELECT t1.c2 FROM ft1 t1 WHERE t1.c1 > 960 ORDER BY t1.c2 FETCH FIRST 2 ROWS WITH TIES; + -- Test CASE pushdown EXPLAIN (VERBOSE, COSTS OFF) SELECT c1,c2,c3 FROM ft2 WHERE CASE WHEN c1 > 990 THEN c1 END < 1000 ORDER BY c1; |