[PR] fix(planner): fix range-pair NULL adjustment under outer joins [cloudberry]

["yjhjstz (via GitHub)" <gi...@apache.org>]

yjhjstz opened a new pull request, #1981:
URL: https://github.com/apache/cloudberry/pull/1981

   
   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
   
   ### What does this PR do?
   <!-- Brief overview of the changes, including any major features or fixes -->
   
   ### Type of Change
   - [ ] Bug fix (non-breaking change)
   - [ ] New feature (non-breaking change)
   - [ ] Breaking change (fix or feature with breaking changes)
   - [ ] Documentation update
   
   ### Breaking Changes
   <!-- Remove if not applicable. If yes, explain impact and migration path -->
   
   ### Test Plan
   <!-- How did you test these changes? -->
   - [ ] Unit tests added/updated
   - [ ] Integration tests added/updated
   - [ ] Passed `make installcheck`
   - [ ] Passed `make -C src/test installcheck-cbdb-parallel`
   
   ### Impact
   <!-- Remove sections that don't apply -->
   **Performance:**
   <!-- Any performance implications? -->
   
   **User-facing changes:**
   <!-- Any changes visible to users? -->
   
   **Dependencies:**
   <!-- New dependencies or version changes? -->
   
   ### Checklist
   - [ ] Followed [contribution guide](https://cloudberry.apache.org/contribute/code)
   - [ ] Added/updated documentation
   - [ ] Reviewed code for security implications
   - [ ] This PR contains AI-assisted code generation
   - [ ] Requested review from [cloudberry committers](https://github.com/orgs/apache/teams/cloudberry-committers)
   
   ### Additional Context
   <!-- Any other information that would help reviewers? Remove if none -->
   
   ### CI Skip Instructions
   <!--
   To skip CI builds, add the appropriate CI skip identifier to your PR title.
   The identifier must:
   - Be in square brackets []
   - Include the word "ci" and either "skip" or "no"
   - Only use for documentation-only changes or when absolutely necessary
   -->
   
   ---
   <!-- Join our community:
   - Mailing list: [dev@cloudberry.apache.org](https://lists.apache.org/list.html?dev@cloudberry.apache.org) (subscribe: dev-subscribe@cloudberry.apache.org)
   - Discussions: https://github.com/apache/cloudberry/discussions -->
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org

Re: [PR] fix(planner): fix range-pair NULL adjustment under outer joins [cloudberry]

["yjhjstz (via GitHub)" <gi...@apache.org>]

yjhjstz merged PR #1981:
URL: https://github.com/apache/cloudberry/pull/1981


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org