Fix #9308 - avoid pruning plan for ANTI join with filter that is always true #9654
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Mytherin
added a commit
that referenced
this pull request
Jun 30, 2025
Closes #17417 This issue appears to actually be a regression of issue #9308 (with the existing test for that issue not picking up the regression as the optimizer no longer makes an explicit ANTI-JOIN for the WHERE NOT EXISTS query). While the fix in #9654 could be re-applied (and if this PR is deemed not suitable, should be) to remove the pruning optimization altogether for ANTI-JOINS with always true filters, this PR aims to keep the optimization and handle the case where the join is on an empty result set. In particular, this PR adds a helper function to BaseStatistics to determine whether the stats match an empty result set (using the min/max set by CreateEmpty) and correctly returns the LHS of the ANTI-JOIN if the RHS is empty (and keeps the current behaviour of returning an empty result otherwise) While this function is only currently used for NumericStats, I've also implemented it for the other BaseStatistics implementations, to avoid this being regressed again in future if statistics propagation (and in particular, filter pruning) occur for non-numeric types.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Fixes #9308
When an ANTI join is performed on a condition that is proven to be true for every row in the data set the result is the empty set ONLY when the RHS is not empty. The current statistics propagation optimizer always returns the empty set which is incorrect if the RHS is empty. This PR removes this specific optimization for anti joins.