Skip to content

Optimizer rule FilterNullJoinKeys causes null-aware joins to return incorrect results #23847

Description

@AdamGS

Describe the bug

The optimizer rule FilterNullJoinKeys doesn't take null-awarness into account, which can cause it to transform the logical plan in an invalid way, returning wrong results.

This behavior isn't enabled by default, but requires setting datafusion.optimizer.filter_null_join_keys to true.

To Reproduce

From null_aware_anti_join.slt, run

set datafusion.optimizer.filter_null_join_keys = true;
SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM inner_table_with_null);

which will return incorrect results, which are different than the default datafusion.optimizer.filter_null_join_keys = false:

1 a
3 c
4 d

Expected behavior

Config changes shouldn't impact correctness

Additional context

No response

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions