IEJoin gives wrong result #10122
Description
What happens?
PhysicalRangeJoin::LocalSortedTable::MergeNulls merges every key's ValidityMask into primary key's ValidityMask, which may mistakenly set primary key to null.
I'm willing to make a PR to fix this.
To Reproduce
create table tt (x int, y int, z int);
insert into tt select nullif(r % 10, 0), nullif (r % 11, 0), r from range(50) tbl(r);
select *
from tt t1 left join tt t2
on t1.x < t2.x and t1.y < t2.y
order by t1.x nulls first, t1.y nulls first, t1.z;
It outputs:
┌───────┬───────┬───────┬───────┬───────┬───────┐
│ x │ y │ z │ x │ y │ z │
│ int32 │ int32 │ int32 │ int32 │ int32 │ int32 │
├───────┼───────┼───────┼───────┼───────┼───────┤
│ │ │ 0 │ │ │ │
│ │ │ 10 │ │ │ │
│ │ │ 20 │ │ │ │
│ │ │ 30 │ │ │ │
│ │ │ 40 │ │ │ │
│ 1 │ │ 11 │ │ │ │
│ 1 │ 1 │ 1 │ 2 │ 2 │ 2 │
│ 1 │ 1 │ 1 │ 3 │ 2 │ 13 │
│ 1 │ 1 │ 1 │ 4 │ 2 │ 24 │
│ 1 │ 1 │ 1 │ 5 │ 2 │ 35 │
│ 1 │ 1 │ 1 │ 6 │ 2 │ 46 │
│ 1 │ 1 │ 1 │ 3 │ 3 │ 3 │
│ 1 │ 1 │ 1 │ 4 │ 3 │ 14 │
│ 1 │ 1 │ 1 │ 5 │ 3 │ 25 │
│ 1 │ 1 │ 1 │ 6 │ 3 │ 36 │
│ 1 │ 1 │ 1 │ 7 │ 3 │ 47 │
Obviously, x and y are both null if and only if z%110==0, the result is wrong.
OS:
ubuntu 22.04
DuckDB Version:
master
DuckDB Client:
CLI
Full Name:
Ke Xu
Affiliation:
Southeast University
Have you tried this on the latest main branch?
I have tested with a main build
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- Yes, I have