viirya commented on code in PR #26214:
URL: https://github.com/apache/datafusion/pull/26214#discussion_r4369374133
##########
datafusion/optimizer/src/decorrelate_predicate_subquery.rs:
##########
@@ -929,6 +934,38 @@ fn build_join(
}
let left = projected_left.as_ref().unwrap_or(left);
+ // The first `V` keys of a null-aware join are its `NOT IN` value keys.
+ //
+ // A tuple element that cannot be NULL in scope on either side never makes
+ // the tuple comparison UNKNOWN: compared with a definite value it is
either
+ // equal, which leaves the other elements to decide, or a definite
mismatch,
+ // which makes the comparison FALSE whatever the other elements are. It
+ // therefore only selects which subquery rows a NULL in another element is
+ // compared with, which is what a correlation scope key does. Moving such
+ // elements after the ones that can be NULL turns them into scope keys, so
+ // the hash join finds the rows a NULL-valued row is compared with through
a
+ // hash lookup instead of pairing it with every row of the other side
+ // (<https://github.com/apache/datafusion/issues/26088>).
+ //
+ // A null-aware tuple has at least one element that can be NULL, so `V` is
+ // at least one. With exactly one, the join is a scalar `NOT IN` on that
+ // element, correlated by the other elements.
+ let null_aware_value_keys = if null_aware && in_values.len() > 1 {
+ let (nullable, non_nullable): (Vec<_>, Vec<_>) = in_values
+ .into_iter()
+ .zip(elements_may_be_null)
+ .partition(|(_, may_be_null)| *may_be_null);
+ let null_aware_value_keys = nullable.len();
Review Comment:
Thanks, confirmed. On the example from your comment, the scope-key path
takes ~300 ms versus ~1 ms when `b = y` stays a value key. With #26213 merged
it now plans as `RightAnti` and regresses the same way, and `LeftMark` too (218
ms vs 1 ms).
The root cause is not specific to this PR: the scope-key marking walks every
pair in a bucket and drops already-marked rows only afterwards, so a correlated
scalar `NOT IN` with a low-cardinality equality correlation already hits it on
main (`a NOT IN (SELECT x FROM inner_t i WHERE i.y = o.b)` takes ~300 ms on the
same data). #25679 works on this by skipping marked rows in the scope lookup
and grouping probe rows by scope hash.
Since this PR only makes that path more common, I'll keep it on hold until a
fix like #25679 lands, and then add a constant/low-cardinality scope-key query
to the `null_aware_join` benchmark here.
--
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]
