This is an automated email from the ASF dual-hosted git repository.
yjhjstz pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new e9903995ff6 fix(planner): fix range-pair NULL adjustment under outer
joins and clamp join selectivities
e9903995ff6 is described below
commit e9903995ff665237ca8628208fa31ee1c6b021dd
Author: Jianghua Yang <[email protected]>
AuthorDate: Sat Sep 5 07:51:48 2026 +0800
fix(planner): fix range-pair NULL adjustment under outer joins and clamp
join selectivities
On an assert-enabled build the following query crashed the QD with
FailedAssertion("pselec >= 0.0 && pselec <= 1.0", costsize.c:5506):
CREATE TABLE m1(c0 inet);
CREATE TABLE m2(c0 inet);
INSERT INTO m2 VALUES ('88.147.138.141'), ('76.163.212.11'),
('214.10.65.144');
ANALYZE m1, m2;
SELECT COUNT(*) FROM ONLY m1 LEFT OUTER JOIN m2 ON true
WHERE (m1.c0 IS NOT NULL)
OR (m2.c0 BETWEEN SYMMETRIC '75.175.243.19' AND '230.9.216.68');
The pushed-down WHERE clause is an OR of an IS NOT NULL test on the
outer side (selectivity 0.999, no stats on the empty table) and a range
pair on the nullable side. When clauselist_selectivity() merges the
range pair (hibound + lobound - 1) it adds back the column's null
fraction to undo the double exclusion of NULLs, and it does so by
calling nulltestsel(IS_NULL, ..., jointype). GPDB's nulltestsel()
special-cases IS NULL under an outer join and returns 0.5 (a guess for
the anti-join "WHERE inner.col IS NULL" pattern, removed upstream in
e006a24a), so the range pair comes out as 0.99 + 0.5 = 1.49 instead of
0.99 + 0.0. Combining that with the IS NOT NULL arm via
s1 + s2 - s1*s2 gives 1.00049, which adjust_selectivity_for_nulltest()
asserts on.
Two changes:
1. In clauselist_selectivity_ext(), ask nulltestsel() for the column's
real null fraction by passing JOIN_INNER. The range-pair correction
is a statistical adjustment, not an IS NULL predicate evaluated at
the join level, so the outer-join guess never belonged here. Before
this, every range pair evaluated under an outer join was inflated by
an absolute 0.5; after it, a LEFT JOIN ON clause with a range
condition gets the same estimate as the equivalent inner join. This
matches upstream behaviour, where nulltestsel() ignores jointype.
2. Selectivities are probabilities, so also clamp jselec and pselec to
[0, 1] in calc_joinrel_size_estimate() before handing them to
adjust_selectivity_for_nulltest(), so round-off in the OR combination
can never trip the assertion again.
Add the SQLancer query to bfv_planner as a regression test.
Fixes #1950
---
src/backend/optimizer/path/clausesel.c | 12 ++++++++++--
src/backend/optimizer/path/costsize.c | 10 +++++++++-
src/test/regress/expected/bfv_planner.out | 20 ++++++++++++++++++++
src/test/regress/expected/bfv_planner_optimizer.out | 20 ++++++++++++++++++++
src/test/regress/sql/bfv_planner.sql | 17 +++++++++++++++++
5 files changed, 76 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/path/clausesel.c
b/src/backend/optimizer/path/clausesel.c
index 2ae20ae2aab..cc35f6b8106 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -336,9 +336,17 @@ clauselist_selectivity_ext(PlannerInfo *root,
{
s2 = rqlist->hibound + rqlist->lobound - 1.0;
- /* Adjust for double-exclusion of NULLs */
+ /*
+ * Adjust for double-exclusion of NULLs.
+ *
+ * We want the column's actual null fraction
here, not the
+ * selectivity of an "IS NULL" test at this
join level. GPDB's
+ * nulltestsel() special-cases IS NULL under an
outer join to
+ * return 0.5 (see GPDB_84_MERGE_NOTE there),
which would push
+ * s2 above 1.0. So always ask as if for an
inner join.
+ */
s2 += nulltestsel(root, IS_NULL, rqlist->var,
- varRelid,
jointype, sjinfo);
+ varRelid,
JOIN_INNER, NULL);
/*
* A zero or slightly negative s2 should be
converted into a
diff --git a/src/backend/optimizer/path/costsize.c
b/src/backend/optimizer/path/costsize.c
index fa7a2f7199e..8042a893575 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -5814,7 +5814,15 @@ calc_joinrel_size_estimate(PlannerInfo *root,
jointype,
sjinfo,
gp_selectivity_damping_for_joins);
-
+
+ /*
+ * Selectivities must be probabilities. Round-off in the OR
+ * combination (s1 + s2 - s1*s2) can leave them a hair outside
[0,1],
+ * and adjust_selectivity_for_nulltest() asserts on that.
+ */
+ CLAMP_PROBABILITY(jselec);
+ CLAMP_PROBABILITY(pselec);
+
/*
* special case where a pushed qual probes the inner
* side of an outer join to be NULL
diff --git a/src/test/regress/expected/bfv_planner.out
b/src/test/regress/expected/bfv_planner.out
index eafe2e8bc15..0c13e5cf546 100644
--- a/src/test/regress/expected/bfv_planner.out
+++ b/src/test/regress/expected/bfv_planner.out
@@ -818,6 +818,26 @@ drop table t1_issue_593;
drop table t2_issue_593;
drop table t3_issue_593;
drop table t4_issue_593;
+--
+-- test https://github.com/apache/cloudberry/issues/1950
+-- A pushed-down OR of an outer-side IS NOT NULL and a range condition on the
+-- nullable side yielded a join selectivity slightly above 1.0 and tripped an
+-- assertion in adjust_selectivity_for_nulltest().
+--
+CREATE TABLE t1_issue_1950(c0 inet) DISTRIBUTED BY (c0);
+CREATE TABLE t2_issue_1950(c0 inet) DISTRIBUTED BY (c0);
+INSERT INTO t2_issue_1950 VALUES ('88.147.138.141'), ('76.163.212.11'),
('214.10.65.144');
+ANALYZE t1_issue_1950, t2_issue_1950;
+SELECT COUNT(*) FROM ONLY t1_issue_1950 LEFT OUTER JOIN t2_issue_1950 ON true
+WHERE (t1_issue_1950.c0 IS NOT NULL)
+ OR (t2_issue_1950.c0 BETWEEN SYMMETRIC '75.175.243.19' AND '230.9.216.68');
+ count
+-------
+ 0
+(1 row)
+
+drop table t1_issue_1950;
+drop table t2_issue_1950;
-- start_ignore
drop table if exists bfv_planner_x;
drop table if exists testbadsql;
diff --git a/src/test/regress/expected/bfv_planner_optimizer.out
b/src/test/regress/expected/bfv_planner_optimizer.out
index 56ab8968024..fc8b0d96bde 100644
--- a/src/test/regress/expected/bfv_planner_optimizer.out
+++ b/src/test/regress/expected/bfv_planner_optimizer.out
@@ -836,6 +836,26 @@ drop table t1_issue_593;
drop table t2_issue_593;
drop table t3_issue_593;
drop table t4_issue_593;
+--
+-- test https://github.com/apache/cloudberry/issues/1950
+-- A pushed-down OR of an outer-side IS NOT NULL and a range condition on the
+-- nullable side yielded a join selectivity slightly above 1.0 and tripped an
+-- assertion in adjust_selectivity_for_nulltest().
+--
+CREATE TABLE t1_issue_1950(c0 inet) DISTRIBUTED BY (c0);
+CREATE TABLE t2_issue_1950(c0 inet) DISTRIBUTED BY (c0);
+INSERT INTO t2_issue_1950 VALUES ('88.147.138.141'), ('76.163.212.11'),
('214.10.65.144');
+ANALYZE t1_issue_1950, t2_issue_1950;
+SELECT COUNT(*) FROM ONLY t1_issue_1950 LEFT OUTER JOIN t2_issue_1950 ON true
+WHERE (t1_issue_1950.c0 IS NOT NULL)
+ OR (t2_issue_1950.c0 BETWEEN SYMMETRIC '75.175.243.19' AND '230.9.216.68');
+ count
+-------
+ 0
+(1 row)
+
+drop table t1_issue_1950;
+drop table t2_issue_1950;
-- start_ignore
drop table if exists bfv_planner_x;
drop table if exists testbadsql;
diff --git a/src/test/regress/sql/bfv_planner.sql
b/src/test/regress/sql/bfv_planner.sql
index c68c73ab5f6..188c9eb0f28 100644
--- a/src/test/regress/sql/bfv_planner.sql
+++ b/src/test/regress/sql/bfv_planner.sql
@@ -468,6 +468,23 @@ drop table t2_issue_593;
drop table t3_issue_593;
drop table t4_issue_593;
+--
+-- test https://github.com/apache/cloudberry/issues/1950
+-- A pushed-down OR of an outer-side IS NOT NULL and a range condition on the
+-- nullable side yielded a join selectivity slightly above 1.0 and tripped an
+-- assertion in adjust_selectivity_for_nulltest().
+--
+CREATE TABLE t1_issue_1950(c0 inet) DISTRIBUTED BY (c0);
+CREATE TABLE t2_issue_1950(c0 inet) DISTRIBUTED BY (c0);
+INSERT INTO t2_issue_1950 VALUES ('88.147.138.141'), ('76.163.212.11'),
('214.10.65.144');
+ANALYZE t1_issue_1950, t2_issue_1950;
+SELECT COUNT(*) FROM ONLY t1_issue_1950 LEFT OUTER JOIN t2_issue_1950 ON true
+WHERE (t1_issue_1950.c0 IS NOT NULL)
+ OR (t2_issue_1950.c0 BETWEEN SYMMETRIC '75.175.243.19' AND '230.9.216.68');
+
+drop table t1_issue_1950;
+drop table t2_issue_1950;
+
-- start_ignore
drop table if exists bfv_planner_x;
drop table if exists testbadsql;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
