wudidapaopao opened a new pull request, #25207:
URL: https://github.com/apache/datafusion/pull/25207
## Which issue does this PR close?
- Closes #5830.
## Rationale for this change
`simplify_predicates` (run by `PushDownFilter`) reduces the `>`/`>=` and the
`<`/`<=` comparisons on a column to their most restrictive bound, but never
compares the two groups with each other, and only looks for contradictions
between equalities. So a filter that no row can satisfy still scans the
table and
evaluates the predicate per row, and comparisons already implied by an
equality
are still evaluated.
| `WHERE` clause | Before | After |
| --- | --- | --- |
| `a > 3 AND a < 1` | `a > 3 AND a < 1` | `EmptyRelation` |
| `a > 1 AND a < 1` | `a > 1 AND a < 1` | `EmptyRelation` |
| `a >= 1 AND a < 1` | `a >= 1 AND a < 1` | `EmptyRelation` |
| `a >= 1 AND a <= 1` | `a >= 1 AND a <= 1` | `a = 1` (unchanged, must not
be pruned) |
| `a = 7 AND a < 2` | `a = 7 AND a < 2` | `EmptyRelation` |
| `a = 7 AND a != 7` | `a = 7 AND a != 7` | `EmptyRelation` |
| `a = 7 AND a > 5` | `a = 7 AND a > 5` | `a = 7` |
| `a = 7 AND a != 6` | `a = 7 AND a != 6` | `a = 7` |
| `a > 10 AND a != 5` | `a > 10 AND a != 5` | `a > 10` |
## What changes are included in this PR?
Three commits, the first of which is an existing bug this analysis would
otherwise build on.
1. `fix`: comparisons against a NULL literal were grouped and reduced like
any
other. `ScalarValue::try_cmp` follows sort order, where NULL is an
ordinary
value below every other one, rather than SQL three-valued logic, so
`a > NULL AND a > 5` was reduced to `a > 5`. `a > NULL` never evaluates to
true, so the conjunction matches no row while `a > 5` does. Queries do not
reach this because `SimplifyExpressions` folds such comparisons
beforehand,
but `simplify_predicates` is public.
2. `feat`: reason across the comparison groups of a column.
- Contradicting bounds reduce the conjunction to `false`, so
`EliminateFilter`
and `PropagateEmptyRelation` can prune the plan being filtered. Bounds
that
meet at one value are unsatisfiable unless both admit it.
- An equality pins the column to a single value, so it subsumes every
other
predicate that value satisfies and contradicts the rest.
- `!=` predicates now take part: one is dropped once a bound already
excludes
its value, and one contradicting an equality reduces the conjunction to
`false`.
A column whose predicates contradict each other short circuits the whole
list, since predicates on other columns cannot make the conjunction true
again.
`false` stands for a conjunction that never evaluates to true, which under
three-valued logic includes evaluating to NULL. That is only equivalent
for
the predicates of a `Filter`, which keeps a row solely when they evaluate
to
true, and is where this runs.
3. `test`: unit tests and `sqllogictest` cases for the above.
## What is the testing strategy for this PR?
Unit tests in `simplify_predicates.rs` cover an equality being subsumed by
and
rejected by each of the six comparison operators, bounds meeting at one
value for
every combination of strict and inclusive comparisons, one column's
contradiction
discarding the predicates on other columns, `!=` being dropped and kept, and
comparisons against NULL staying untouched. The last is only reachable from a
unit test, as noted above.
`simplify_predicates.slt` checks the plans produced for the same cases, and
drops
two `# TODO` markers that this PR implements.
Verified with:
```text
./dev/rust_lint.sh
cargo test -p datafusion-optimizer
cargo test -p datafusion
cargo test -p datafusion-cli
cargo test --profile=ci --test sqllogictests
```
## Are there any user-facing changes?
Filters that no row can satisfy no longer scan their input, and redundant
comparisons are no longer evaluated per row. Result sets are unchanged, and
there
are no changes to public APIs.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
