From 084036113756fd8e75ccc43e7e2c3b8d641fcc33 Mon Sep 17 00:00:00 2001 From: Robins Tharakan Date: Sat, 31 Oct 2026 20:08:49 +1030 Subject: [PATCH v1] postgres_fdw: Fix assertion with redundant ORDER BY in set-op input add_foreign_ordered_paths() assumed that for a base or join relation, query_pathkeys is the same list as sort_pathkeys, because standard_qp_callback() sets it so whenever there is an ORDER BY to do. Since commit 12933dc6048, standard_qp_callback() instead falls back to setop_pathkeys when sort_pathkeys is NIL, for a subquery planned as an input of a set operation. An ORDER BY whose keys are all redundant (constants, or columns that the WHERE clause equates to constants) leaves sort_pathkeys NIL while the query still has a sortClause, so create_ordered_paths() still calls the FDW for UPPERREL_ORDERED, and the assertion failed: (SELECT a, b FROM ft WHERE a = 1 ORDER BY a) UNION (SELECT 2, true); From v18, inputs of EXCEPT and INTERSECT are affected too, since those set operations can now use presorted input. Non-assert builds returned correct results, but they set the ordered relation's pushdown_safe from qp_is_pushdown_safe, which was computed for setop_pathkeys rather than for the (empty) final sort. So whether a LIMIT in such a subquery could be pushed down depended on whether the set operation's sort order could be shipped. Fix by handling that case first: with no final sort to do, the ordered relation is safe to push down, as it already is for a grouping relation whose ORDER BY keys are all redundant. The assertion now checks that this is the only way query_pathkeys can differ from sort_pathkeys here. Backpatch-through: 17 --- .../postgres_fdw/expected/postgres_fdw.out | 37 +++++++++++++++++++ contrib/postgres_fdw/postgres_fdw.c | 17 ++++++++- contrib/postgres_fdw/sql/postgres_fdw.sql | 9 +++++ 3 files changed, 62 insertions(+), 1 deletion(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 115ebc7..2bfd481 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -1382,6 +1382,43 @@ SELECT * FROM ( Remote SQL: SELECT "C 1" FROM "S 1"."T 1" (8 rows) +-- A set operation's input whose ORDER BY keys are all redundant has no sort +-- to push down, though query_pathkeys holds the set operation's pathkeys. +(SELECT c1, c2 FROM ft1 WHERE c1 = 1 ORDER BY c1) UNION (SELECT 2, 3) ORDER BY 1; + c1 | c2 +----+---- + 1 | 1 + 2 | 3 +(2 rows) + +(SELECT t1.c1, t2.c2 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c1 = 1 ORDER BY t1.c1) +UNION (SELECT 2, 3) ORDER BY 1; + c1 | c2 +----+---- + 1 | 1 + 2 | 3 +(2 rows) + +(SELECT c1, c2 FROM ft1 WHERE c1 = 1 ORDER BY c1 LIMIT 1) UNION (SELECT 2, 3) ORDER BY 1; + c1 | c2 +----+---- + 1 | 1 + 2 | 3 +(2 rows) + +(SELECT 1 AS x, c2 FROM ft1 WHERE c1 <= 3 ORDER BY 1) EXCEPT (SELECT 1, 2) ORDER BY 2; + x | c2 +---+---- + 1 | 1 + 1 | 3 +(2 rows) + +(SELECT c1, c2 FROM ft1 WHERE c1 = 1 ORDER BY c1) INTERSECT (SELECT 1, 1); + c1 | c2 +----+---- + 1 | 1 +(1 row) + -- =================================================================== -- JOIN queries -- =================================================================== diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 6757ee9..135535b 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -8277,7 +8277,22 @@ add_foreign_ordered_paths(PlannerInfo *root, RelOptInfo *input_rel, if (input_rel->reloptkind == RELOPT_BASEREL || input_rel->reloptkind == RELOPT_JOINREL) { - Assert(root->query_pathkeys == root->sort_pathkeys); + /* + * The exception is a subquery planned as an input of a set operation + * whose ORDER BY keys are all redundant (for instance, constants, or + * columns that the WHERE clause equates to constants). Then + * sort_pathkeys is NIL and query_pathkeys is the setop_pathkeys. + * There is no final sort to do, so nothing here is unsafe to push + * down. qp_is_pushdown_safe doesn't apply, since it was computed for + * the setop_pathkeys. + */ + if (root->query_pathkeys != root->sort_pathkeys) + { + Assert(root->sort_pathkeys == NIL && + root->query_pathkeys == root->setop_pathkeys); + fpinfo->pushdown_safe = true; + return; + } /* Safe to push down if the query_pathkeys is safe to push down */ fpinfo->pushdown_safe = ifpinfo->qp_is_pushdown_safe; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 77c9a25..16e07af 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -542,6 +542,15 @@ SELECT * FROM ( SELECT 2 AS type,c1 FROM ft2 ) a ORDER BY type; +-- A set operation's input whose ORDER BY keys are all redundant has no sort +-- to push down, though query_pathkeys holds the set operation's pathkeys. +(SELECT c1, c2 FROM ft1 WHERE c1 = 1 ORDER BY c1) UNION (SELECT 2, 3) ORDER BY 1; +(SELECT t1.c1, t2.c2 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c1 = 1 ORDER BY t1.c1) +UNION (SELECT 2, 3) ORDER BY 1; +(SELECT c1, c2 FROM ft1 WHERE c1 = 1 ORDER BY c1 LIMIT 1) UNION (SELECT 2, 3) ORDER BY 1; +(SELECT 1 AS x, c2 FROM ft1 WHERE c1 <= 3 ORDER BY 1) EXCEPT (SELECT 1, 2) ORDER BY 2; +(SELECT c1, c2 FROM ft1 WHERE c1 = 1 ORDER BY c1) INTERSECT (SELECT 1, 1); + -- =================================================================== -- JOIN queries -- ===================================================================