public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: PostgreSQL mailing lists <[email protected]>
Subject: MergeJoin fails on incomplete btree opfamily definition
Date: Wed, 15 Apr 2026 10:20:54 +0200
Message-ID: <[email protected]> (raw)
Hi,
While experimenting with chaotic cost assignment [1], I found that
equivclass.sql contains a query that sometimes fails with an error:
-- let's try that as a mergejoin
set enable_mergejoin = on;
set enable_nestloop = off;
explain (costs off)
select * from ec1,
(select ff + 1 as x from
...
The underlying problem is simple: an incomplete btree operator family.
That would be acceptable if the behaviour were consistent, but as the
attached self-contained SQL script shows, it is unstable and depends on
which sort direction the planner happens to choose for the merge join's
internal sort — the same catalogue state can yield either a valid plan
or an elog(ERROR) depending on cost-driven decisions.
The main reason to fix this: when the opfamily lacks the ordering
operator required to sort one side of a merge, the planner should skip
the MergeJoin strategy and fall back to another join method, rather than
constructing an unusable plan and crashing in
prepare_sort_from_pathkeys(). I have verified the issue reproduces on
current master. This code is unchanged for several releases, so it seems
worth fixing and back-patching.
The issue actually has two layers. The "missing operator" error is the
first one. There is a related "missing support function" error on the
same path — I'll discover it later if there is interest in this topic.
See the proposed fix for the current problem.
[1] https://github.com/danolivo/pg-chaos-test
--
regards, Andrei Lepikhov,
pgEdge
BEGIN;
CREATE TYPE int8alias1;
CREATE FUNCTION int8alias1in(cstring) RETURNS int8alias1
STRICT IMMUTABLE LANGUAGE internal AS 'int8in';
CREATE FUNCTION int8alias1out(int8alias1) RETURNS cstring
STRICT IMMUTABLE LANGUAGE internal AS 'int8out';
CREATE TYPE int8alias1 (input = int8alias1in, output = int8alias1out, like = int8);
CREATE CAST (int8 AS int8alias1) WITHOUT FUNCTION;
CREATE FUNCTION int8alias1eq(int8alias1, int8alias1) RETURNS bool
STRICT IMMUTABLE LANGUAGE internal AS 'int8eq';
CREATE OPERATOR = (
procedure = int8alias1eq,
leftarg = int8alias1, rightarg = int8alias1,
commutator = =, merges
);
CREATE FUNCTION int8alias1lt(int8alias1, int8alias1) RETURNS bool
STRICT IMMUTABLE LANGUAGE internal AS 'int8lt';
CREATE OPERATOR < (procedure = int8alias1lt,
leftarg = int8alias1, rightarg = int8alias1);
CREATE FUNCTION int8alias1gt(int8alias1, int8alias1) RETURNS bool
STRICT IMMUTABLE LANGUAGE internal AS 'int8gt';
CREATE OPERATOR > (procedure = int8alias1gt,
leftarg = int8alias1, rightarg = int8alias1);
CREATE FUNCTION int8alias1cmp(int8alias1, int8alias1) RETURNS int
STRICT IMMUTABLE LANGUAGE internal AS 'btint8cmp';
ALTER OPERATOR FAMILY integer_ops USING btree ADD
OPERATOR 3 = (int8alias1, int8alias1),
-- OPERATOR 1 < (int8alias1, int8alias1),
OPERATOR 5 > (int8alias1, int8alias1),
FUNCTION 1 int8alias1cmp(int8alias1, int8alias1);
CREATE TABLE t1 (b int8alias1);
CREATE TABLE t2 (b int8alias1);
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = on;
EXPLAIN (COSTS OFF) SELECT * FROM t1 JOIN t2 ON t1.b = t2.b;
ROLLBACK;
/*
Expected on vanilla PostgreSQL:
ERROR: missing operator 1(1...) in opfamily 1976
Expected with the patch applied:
QUERY PLAN
------------------------------------
Nested Loop
Disabled: true
Join Filter: (t1.b = t2.b)
-> Seq Scan on t1
-> Seq Scan on t2
(5 rows)
*/
From ce373a44fe5202e24734c4122f0179362ff5a0a7 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Wed, 15 Apr 2026 08:01:41 +0200
Subject: [PATCH v0] Skip merge join paths when opfamily lacks ordering
operators
An incomplete btree opfamily definition could cause the planner to construct
a merge join path that cannot be implemented.
---
src/backend/optimizer/path/equivclass.c | 39 ++++++++++++++++++++++++
src/backend/optimizer/path/joinpath.c | 23 ++++++++++++++
src/backend/optimizer/path/pathkeys.c | 27 ++++++++++++++++
src/include/optimizer/paths.h | 3 ++
src/test/regress/expected/equivclass.out | 36 ++++++++++++++++++++++
src/test/regress/sql/equivclass.sql | 26 ++++++++++++++++
6 files changed, 154 insertions(+)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index e3697df51a2..67904bb5f47 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1137,6 +1137,45 @@ relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
return true;
}
+/*
+ * ec_has_sortable_member
+ * Check whether an EquivalenceClass has at least one non-constant member
+ * belonging to 'relids' for which a sort operator of the given compare
+ * type exists in the specified opfamily. Returns false if no qualifying
+ * member has a valid sort operator, which can happen with incomplete
+ * opfamily definitions.
+ */
+bool
+ec_has_sortable_member(EquivalenceClass *ec, Relids relids,
+ Oid opfamily, CompareType cmptype)
+{
+ EquivalenceMemberIterator it;
+ EquivalenceMember *em;
+
+ setup_eclass_member_iterator(&it, ec, relids);
+ while ((em = eclass_member_iterator_next(&it)) != NULL)
+ {
+ if (em->em_is_const)
+ continue;
+
+ /*
+ * Only consider members that belong to the specified relation.
+ * The iterator returns all non-child members regardless of relids,
+ * so we must filter explicitly.
+ */
+ if (!bms_is_subset(em->em_relids, relids))
+ continue;
+
+ if (OidIsValid(get_opfamily_member_for_cmptype(opfamily,
+ em->em_datatype,
+ em->em_datatype,
+ cmptype)))
+ return true;
+ }
+
+ return false;
+}
+
/*
* generate_base_implied_equalities
* Generate any restriction clauses that we can deduce from equivalence
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 713283a73aa..210d49a4629 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -1535,6 +1535,15 @@ sort_inner_and_outer(PlannerInfo *root,
cur_mergeclauses,
outerkeys);
+ /*
+ * Verify that both sides can actually be sorted on these pathkeys.
+ * An incomplete opfamily (e.g. equality without ordering operators)
+ * could pass the mergejoinable check but fail at sort time.
+ */
+ if (!pathkeys_are_sortable_for_rel(outerkeys, outerrel->relids) ||
+ !pathkeys_are_sortable_for_rel(innerkeys, innerrel->relids))
+ continue;
+
/* Build pathkeys representing output sort order */
merge_pathkeys = build_join_pathkeys(root, joinrel, jointype,
outerkeys);
@@ -1641,6 +1650,20 @@ generate_mergejoin_paths(PlannerInfo *root,
mergeclauses,
outerpath->pathkeys);
+ /*
+ * Verify that the inner side can be sorted on these pathkeys. An
+ * incomplete opfamily (e.g. equality without ordering operators) could
+ * allow a mergejoin path that cannot be implemented.
+ *
+ * We only check the inner side here because the outer path is already
+ * ordered (this function is called from match_unsorted_outer where the
+ * outer path's existing pathkeys drive the merge). The outer side
+ * never needs an explicit sort, so outersortkeys is always NIL in the
+ * try_mergejoin_path call below.
+ */
+ if (!pathkeys_are_sortable_for_rel(innersortkeys, innerrel->relids))
+ return;
+
/*
* Generate a mergejoin on the basis of sorting the cheapest inner. Since
* a sort will be needed, only cheapest total cost matters. (But
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 5eb71635d15..83e5e42d393 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -353,6 +353,33 @@ pathkeys_contained_in(List *keys1, List *keys2)
return false;
}
+/*
+ * pathkeys_are_sortable_for_rel
+ * Check whether every pathkey in the list has a usable sort operator
+ * for at least one EquivalenceClass member belonging to 'relids'.
+ *
+ * Returns false if any pathkey's opfamily lacks the required ordering
+ * operator for all EC members of the given relation. This catches
+ * incomplete opfamily definitions (e.g. equality registered without
+ * corresponding ordering operators) that would cause an error later
+ * in prepare_sort_from_pathkeys().
+ */
+bool
+pathkeys_are_sortable_for_rel(List *pathkeys, Relids relids)
+{
+ ListCell *lc;
+
+ foreach(lc, pathkeys)
+ {
+ PathKey *pk = (PathKey *) lfirst(lc);
+
+ if (!ec_has_sortable_member(pk->pk_eclass, relids,
+ pk->pk_opfamily, pk->pk_cmptype))
+ return false;
+ }
+ return true;
+}
+
/*
* group_keys_reorder_by_pathkeys
* Reorder GROUP BY pathkeys and clauses to match the input pathkeys.
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 17f2099ec3b..6b9adca2921 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -157,6 +157,8 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec,
bool require_parallel_safe);
+extern bool ec_has_sortable_member(EquivalenceClass *ec, Relids relids,
+ Oid opfamily, CompareType cmptype);
extern void generate_base_implied_equalities(PlannerInfo *root);
extern List *generate_join_implied_equalities(PlannerInfo *root,
Relids join_relids,
@@ -225,6 +227,7 @@ typedef enum
extern PathKeysComparison compare_pathkeys(List *keys1, List *keys2);
extern bool pathkeys_contained_in(List *keys1, List *keys2);
extern bool pathkeys_count_contained_in(List *keys1, List *keys2, int *n_common);
+extern bool pathkeys_are_sortable_for_rel(List *pathkeys, Relids relids);
extern List *get_useful_group_keys_orderings(PlannerInfo *root, Path *path);
extern Path *get_cheapest_path_for_pathkeys(List *paths, List *pathkeys,
Relids required_outer,
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index ad8ab294ff6..7c7ac218b0d 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -485,6 +485,42 @@ explain (costs off) -- this should not require a sort
Filter: (f1 = 'foo'::name)
(2 rows)
+--
+-- check that incomplete opfamily does not cause merge join to crash.
+-- the < operator for int8alias1 is temporarily removed from the opfamily
+-- to simulate an incomplete definition (equality without ordering operators).
+--
+begin;
+alter operator family integer_ops using btree drop operator 1 (int8alias1, int8alias1);
+set enable_nestloop = off;
+set enable_hashjoin = off;
+set enable_mergejoin = on;
+-- should fall back to non-merge plan without error
+explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop
+ Disabled: true
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = '42'::bigint)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_1
+ Index Cond: (((ff + 2) + 1) = ec1.f1)
+ -> Seq Scan on ec1 ec1_2
+ Filter: (((ff + 3) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr4 on ec1 ec1_3
+ Index Cond: ((ff + 4) = ec1.f1)
+(11 rows)
+
+abort;
--
-- test handling of merge/hash clauses that do not have valid commutators
--
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql
index 7fc2159349b..b06c23c0520 100644
--- a/src/test/regress/sql/equivclass.sql
+++ b/src/test/regress/sql/equivclass.sql
@@ -286,6 +286,32 @@ create temp view overview as
explain (costs off) -- this should not require a sort
select * from overview where sqli = 'foo' order by sqli;
+--
+-- check that incomplete opfamily does not cause merge join to crash.
+-- the < operator for int8alias1 is temporarily removed from the opfamily
+-- to simulate an incomplete definition (equality without ordering operators).
+--
+begin;
+
+alter operator family integer_ops using btree drop operator 1 (int8alias1, int8alias1);
+
+set enable_nestloop = off;
+set enable_hashjoin = off;
+set enable_mergejoin = on;
+
+-- should fall back to non-merge plan without error
+explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+
+abort;
+
--
-- test handling of merge/hash clauses that do not have valid commutators
--
--
2.53.0
Attachments:
[text/plain] repro_layer_1.sql (1.9K, 2-repro_layer_1.sql)
download | inline:
BEGIN;
CREATE TYPE int8alias1;
CREATE FUNCTION int8alias1in(cstring) RETURNS int8alias1
STRICT IMMUTABLE LANGUAGE internal AS 'int8in';
CREATE FUNCTION int8alias1out(int8alias1) RETURNS cstring
STRICT IMMUTABLE LANGUAGE internal AS 'int8out';
CREATE TYPE int8alias1 (input = int8alias1in, output = int8alias1out, like = int8);
CREATE CAST (int8 AS int8alias1) WITHOUT FUNCTION;
CREATE FUNCTION int8alias1eq(int8alias1, int8alias1) RETURNS bool
STRICT IMMUTABLE LANGUAGE internal AS 'int8eq';
CREATE OPERATOR = (
procedure = int8alias1eq,
leftarg = int8alias1, rightarg = int8alias1,
commutator = =, merges
);
CREATE FUNCTION int8alias1lt(int8alias1, int8alias1) RETURNS bool
STRICT IMMUTABLE LANGUAGE internal AS 'int8lt';
CREATE OPERATOR < (procedure = int8alias1lt,
leftarg = int8alias1, rightarg = int8alias1);
CREATE FUNCTION int8alias1gt(int8alias1, int8alias1) RETURNS bool
STRICT IMMUTABLE LANGUAGE internal AS 'int8gt';
CREATE OPERATOR > (procedure = int8alias1gt,
leftarg = int8alias1, rightarg = int8alias1);
CREATE FUNCTION int8alias1cmp(int8alias1, int8alias1) RETURNS int
STRICT IMMUTABLE LANGUAGE internal AS 'btint8cmp';
ALTER OPERATOR FAMILY integer_ops USING btree ADD
OPERATOR 3 = (int8alias1, int8alias1),
-- OPERATOR 1 < (int8alias1, int8alias1),
OPERATOR 5 > (int8alias1, int8alias1),
FUNCTION 1 int8alias1cmp(int8alias1, int8alias1);
CREATE TABLE t1 (b int8alias1);
CREATE TABLE t2 (b int8alias1);
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = on;
EXPLAIN (COSTS OFF) SELECT * FROM t1 JOIN t2 ON t1.b = t2.b;
ROLLBACK;
/*
Expected on vanilla PostgreSQL:
ERROR: missing operator 1(1...) in opfamily 1976
Expected with the patch applied:
QUERY PLAN
------------------------------------
Nested Loop
Disabled: true
Join Filter: (t1.b = t2.b)
-> Seq Scan on t1
-> Seq Scan on t2
(5 rows)
*/
[text/plain] v0-0001-Skip-merge-join-paths-when-opfamily-lacks-orderin.patch (9.3K, 3-v0-0001-Skip-merge-join-paths-when-opfamily-lacks-orderin.patch)
download | inline diff:
From ce373a44fe5202e24734c4122f0179362ff5a0a7 Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <[email protected]>
Date: Wed, 15 Apr 2026 08:01:41 +0200
Subject: [PATCH v0] Skip merge join paths when opfamily lacks ordering
operators
An incomplete btree opfamily definition could cause the planner to construct
a merge join path that cannot be implemented.
---
src/backend/optimizer/path/equivclass.c | 39 ++++++++++++++++++++++++
src/backend/optimizer/path/joinpath.c | 23 ++++++++++++++
src/backend/optimizer/path/pathkeys.c | 27 ++++++++++++++++
src/include/optimizer/paths.h | 3 ++
src/test/regress/expected/equivclass.out | 36 ++++++++++++++++++++++
src/test/regress/sql/equivclass.sql | 26 ++++++++++++++++
6 files changed, 154 insertions(+)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index e3697df51a2..67904bb5f47 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -1137,6 +1137,45 @@ relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
return true;
}
+/*
+ * ec_has_sortable_member
+ * Check whether an EquivalenceClass has at least one non-constant member
+ * belonging to 'relids' for which a sort operator of the given compare
+ * type exists in the specified opfamily. Returns false if no qualifying
+ * member has a valid sort operator, which can happen with incomplete
+ * opfamily definitions.
+ */
+bool
+ec_has_sortable_member(EquivalenceClass *ec, Relids relids,
+ Oid opfamily, CompareType cmptype)
+{
+ EquivalenceMemberIterator it;
+ EquivalenceMember *em;
+
+ setup_eclass_member_iterator(&it, ec, relids);
+ while ((em = eclass_member_iterator_next(&it)) != NULL)
+ {
+ if (em->em_is_const)
+ continue;
+
+ /*
+ * Only consider members that belong to the specified relation.
+ * The iterator returns all non-child members regardless of relids,
+ * so we must filter explicitly.
+ */
+ if (!bms_is_subset(em->em_relids, relids))
+ continue;
+
+ if (OidIsValid(get_opfamily_member_for_cmptype(opfamily,
+ em->em_datatype,
+ em->em_datatype,
+ cmptype)))
+ return true;
+ }
+
+ return false;
+}
+
/*
* generate_base_implied_equalities
* Generate any restriction clauses that we can deduce from equivalence
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c
index 713283a73aa..210d49a4629 100644
--- a/src/backend/optimizer/path/joinpath.c
+++ b/src/backend/optimizer/path/joinpath.c
@@ -1535,6 +1535,15 @@ sort_inner_and_outer(PlannerInfo *root,
cur_mergeclauses,
outerkeys);
+ /*
+ * Verify that both sides can actually be sorted on these pathkeys.
+ * An incomplete opfamily (e.g. equality without ordering operators)
+ * could pass the mergejoinable check but fail at sort time.
+ */
+ if (!pathkeys_are_sortable_for_rel(outerkeys, outerrel->relids) ||
+ !pathkeys_are_sortable_for_rel(innerkeys, innerrel->relids))
+ continue;
+
/* Build pathkeys representing output sort order */
merge_pathkeys = build_join_pathkeys(root, joinrel, jointype,
outerkeys);
@@ -1641,6 +1650,20 @@ generate_mergejoin_paths(PlannerInfo *root,
mergeclauses,
outerpath->pathkeys);
+ /*
+ * Verify that the inner side can be sorted on these pathkeys. An
+ * incomplete opfamily (e.g. equality without ordering operators) could
+ * allow a mergejoin path that cannot be implemented.
+ *
+ * We only check the inner side here because the outer path is already
+ * ordered (this function is called from match_unsorted_outer where the
+ * outer path's existing pathkeys drive the merge). The outer side
+ * never needs an explicit sort, so outersortkeys is always NIL in the
+ * try_mergejoin_path call below.
+ */
+ if (!pathkeys_are_sortable_for_rel(innersortkeys, innerrel->relids))
+ return;
+
/*
* Generate a mergejoin on the basis of sorting the cheapest inner. Since
* a sort will be needed, only cheapest total cost matters. (But
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c
index 5eb71635d15..83e5e42d393 100644
--- a/src/backend/optimizer/path/pathkeys.c
+++ b/src/backend/optimizer/path/pathkeys.c
@@ -353,6 +353,33 @@ pathkeys_contained_in(List *keys1, List *keys2)
return false;
}
+/*
+ * pathkeys_are_sortable_for_rel
+ * Check whether every pathkey in the list has a usable sort operator
+ * for at least one EquivalenceClass member belonging to 'relids'.
+ *
+ * Returns false if any pathkey's opfamily lacks the required ordering
+ * operator for all EC members of the given relation. This catches
+ * incomplete opfamily definitions (e.g. equality registered without
+ * corresponding ordering operators) that would cause an error later
+ * in prepare_sort_from_pathkeys().
+ */
+bool
+pathkeys_are_sortable_for_rel(List *pathkeys, Relids relids)
+{
+ ListCell *lc;
+
+ foreach(lc, pathkeys)
+ {
+ PathKey *pk = (PathKey *) lfirst(lc);
+
+ if (!ec_has_sortable_member(pk->pk_eclass, relids,
+ pk->pk_opfamily, pk->pk_cmptype))
+ return false;
+ }
+ return true;
+}
+
/*
* group_keys_reorder_by_pathkeys
* Reorder GROUP BY pathkeys and clauses to match the input pathkeys.
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 17f2099ec3b..6b9adca2921 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -157,6 +157,8 @@ extern EquivalenceMember *find_computable_ec_member(PlannerInfo *root,
extern bool relation_can_be_sorted_early(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec,
bool require_parallel_safe);
+extern bool ec_has_sortable_member(EquivalenceClass *ec, Relids relids,
+ Oid opfamily, CompareType cmptype);
extern void generate_base_implied_equalities(PlannerInfo *root);
extern List *generate_join_implied_equalities(PlannerInfo *root,
Relids join_relids,
@@ -225,6 +227,7 @@ typedef enum
extern PathKeysComparison compare_pathkeys(List *keys1, List *keys2);
extern bool pathkeys_contained_in(List *keys1, List *keys2);
extern bool pathkeys_count_contained_in(List *keys1, List *keys2, int *n_common);
+extern bool pathkeys_are_sortable_for_rel(List *pathkeys, Relids relids);
extern List *get_useful_group_keys_orderings(PlannerInfo *root, Path *path);
extern Path *get_cheapest_path_for_pathkeys(List *paths, List *pathkeys,
Relids required_outer,
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index ad8ab294ff6..7c7ac218b0d 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -485,6 +485,42 @@ explain (costs off) -- this should not require a sort
Filter: (f1 = 'foo'::name)
(2 rows)
+--
+-- check that incomplete opfamily does not cause merge join to crash.
+-- the < operator for int8alias1 is temporarily removed from the opfamily
+-- to simulate an incomplete definition (equality without ordering operators).
+--
+begin;
+alter operator family integer_ops using btree drop operator 1 (int8alias1, int8alias1);
+set enable_nestloop = off;
+set enable_hashjoin = off;
+set enable_mergejoin = on;
+-- should fall back to non-merge plan without error
+explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop
+ Disabled: true
+ -> Index Scan using ec1_pkey on ec1
+ Index Cond: (ff = '42'::bigint)
+ -> Append
+ -> Index Scan using ec1_expr2 on ec1 ec1_1
+ Index Cond: (((ff + 2) + 1) = ec1.f1)
+ -> Seq Scan on ec1 ec1_2
+ Filter: (((ff + 3) + 1) = ec1.f1)
+ -> Index Scan using ec1_expr4 on ec1 ec1_3
+ Index Cond: ((ff + 4) = ec1.f1)
+(11 rows)
+
+abort;
--
-- test handling of merge/hash clauses that do not have valid commutators
--
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql
index 7fc2159349b..b06c23c0520 100644
--- a/src/test/regress/sql/equivclass.sql
+++ b/src/test/regress/sql/equivclass.sql
@@ -286,6 +286,32 @@ create temp view overview as
explain (costs off) -- this should not require a sort
select * from overview where sqli = 'foo' order by sqli;
+--
+-- check that incomplete opfamily does not cause merge join to crash.
+-- the < operator for int8alias1 is temporarily removed from the opfamily
+-- to simulate an incomplete definition (equality without ordering operators).
+--
+begin;
+
+alter operator family integer_ops using btree drop operator 1 (int8alias1, int8alias1);
+
+set enable_nestloop = off;
+set enable_hashjoin = off;
+set enable_mergejoin = on;
+
+-- should fall back to non-merge plan without error
+explain (costs off)
+ select * from ec1,
+ (select ff + 1 as x from
+ (select ff + 2 as ff from ec1
+ union all
+ select ff + 3 as ff from ec1) ss0
+ union all
+ select ff + 4 as x from ec1) as ss1
+ where ss1.x = ec1.f1 and ec1.ff = 42::int8;
+
+abort;
+
--
-- test handling of merge/hash clauses that do not have valid commutators
--
--
2.53.0
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: MergeJoin fails on incomplete btree opfamily definition
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox