SubhamSinghal opened a new issue, #25957:
URL: https://github.com/apache/datafusion/issues/25957
### Describe the bug
With datafusion.optimizer.enable_piecewise_merge_join = true,
PiecewiseMergeJoinExec mishandles nested join keys:
List keys with NULL elements give wrong results for < / <=. PWMJ returns
rows that NestedLoopJoinExec doesn't, and misses rows that it does. This
affects the classic joins (Inner/Left/Right/Full) and the existence joins
(EXISTS / NOT EXISTS). > / >= are correct.
Struct keys fail at runtime for every operator: Sort not supported for data
type Struct(...). The planner still picks PWMJ for them, while NLJ runs the
same query correctly.
PWMJ is off by default, so this only affects users who enable it.
### To Reproduce
```sql
set datafusion.execution.target_partitions = 1;
set datafusion.optimizer.enable_piecewise_merge_join = true;
-- SQL orders a NULL element lowest:
select make_array(5) < make_array(arrow_cast(NULL, 'Int64'));
-- false
select named_struct('x', 5) < named_struct('x', arrow_cast(NULL, 'Int64'));
-- false
create table l as select * from (values (make_array(5), 'l5')) t(a, p);
create table r as select * from (values (make_array(arrow_cast(NULL,
'Int64')), 'rnull'),
(make_array(7), 'r7'),
(make_array(1), 'r1')) t(b, q);
create table ln as select * from (values (make_array(arrow_cast(NULL,
'Int64')), 'lnull')) t(a, p);
create table r5 as select * from (values (make_array(5), 'r5')) t(b, q);
create table sl as select * from (values (named_struct('x', 5), 'l5')) t(a,
p);
create table sr as select * from (values (named_struct('x', 7), 'r7'),
(named_struct('x', 1), 'r1')) t(b, q);
```
| query | PWMJ | NLJ (flag off) |
|---|---|---|
| `select l.p, r.q from l join r on l.a < r.b` | `(l5, r7)`, `(l5, rnull)` |
`(l5, r7)` |
| `select l.p, r.q from l right join r on l.a < r.b` | `rnull` matched with
`l5` | `rnull` unmatched |
| `select p from l where exists (select 1 from r where l.a < r.b and r.q =
'rnull')` | `l5` | no rows |
| `select ln.p, r5.q from ln join r5 on ln.a < r5.b` (NULL element on the
buffered side) | no rows | `(lnull, r5)` |
| `select l.p, r.q from l join r on l.a > r.b` | `(l5, r1)`, `(l5, rnull)` |
same |
| `select sl.p, sr.q from sl join sr on sl.a < sr.b` (or `>`) | `Arrow
error: Compute error: Sort not supported for data type Struct("x": Int64)` |
`(l5, r7)` |
### Expected behavior
PWMJ returns the same rows as `NestedLoopJoinExec` for every key type it is
planned for. If it can't, the planner falls back to NLJ.
### Additional context
_No response_
--
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]
