jayzhan211 commented on PR #25338:
URL: https://github.com/apache/datafusion/pull/25338#issuecomment-5697075935
Thanks @adriangb , here is a suggestion:
**Correlated `NOT IN` with a scalar-function key now fails to plan**
The `LeftAnti` branch now reads nullability from the key expressions. Most
scalar UDFs use the default `return_field_from_args`, which always says the
result can be NULL (`datafusion/expr/src/udf.rs`). So a key like `upper(s)`
over a `NOT NULL` column now turns on `null_aware`.
For a correlated `NOT IN` in `WHERE`, that join has 2+ keys (value +
correlation). `HashJoinExec` rejects null-aware `LeftAnti` with more than one
key, and null-aware joins can't fall back to a sort-merge join:
```sql
CREATE TABLE t1(k INT NOT NULL, s VARCHAR NOT NULL) AS VALUES (1, 'a');
CREATE TABLE t2(k INT NOT NULL, s VARCHAR NOT NULL) AS VALUES (1, 'B');
SELECT * FROM t1 WHERE upper(t1.s) NOT IN (SELECT t2.s FROM t2 WHERE t2.k =
t1.k);
-- main: plans a plain LeftAnti and returns (1, 'a')
-- this PR: expected "null_aware LeftAnti joins only support single column
join key"
```
Nullable columns already fail like this on main (#25347), but this change
extends the failure to common function keys over non-nullable data. It also
pins uncorrelated `lower(s) NOT IN (...)` over `NOT NULL` columns to a
`CollectLeft` null-aware join for no correctness gain.
Suggest limiting the expression-level check on the `LeftAnti` path to the
single-key case until #25347 is fixed:
```diff
let null_aware = if join_type == JoinType::LeftAnti &&
in_predicate_opt.is_some() {
let (equijoin_keys, residual_filter) =
split_eq_and_noneq_join_predicate(
join_filter.clone(),
left.schema(),
sub_query_alias.schema(),
)?;
- join_keys_may_be_null(
- &equijoin_keys,
- residual_filter.as_ref(),
- left.schema(),
- sub_query_alias.schema(),
- )?
+ if equijoin_keys.len() == 1 {
+ join_keys_may_be_null(
+ &equijoin_keys,
+ residual_filter.as_ref(),
+ left.schema(),
+ sub_query_alias.schema(),
+ )?
+ } else {
+ // Null-aware LeftAnti supports a single key only (#25347);
keep the
+ // previous column-based test so correlated NOT IN still plans.
+ join_filter_columns_may_be_null(&join_filter, left.schema(),
sub_query_alias.schema())?
+ }
} else {
```
Please also add an slt case with the correlated query above.
--
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]
