You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Left join doesn't preserve order of left for right missing values #37542
I know there's no explicit guaranty on the order of the output data on the join, but it's weird that it preserve order when values are present, and changes it when they are missing.
This is an implementation specific behavior, i.e., depending on the underlying hash join algorithm. Currently there are two types of hash join implementation in acero, both having the similar logic. When doing left outer or full outer joins, the matched rows in the left side are first collected, by probing the hash table built from the right side. Then the unmatched rows in the left side are collected. This results in the order you observed. You can check the code below:
For hash join (the normal algorithm), collecting the unmatched rows:
I know there's no explicit guaranty on the order of the output data on the join, but it's weird that it preserve order when values are present, and changes it when they are missing.
Yes, you shouldn't make any assumption of ordering in SQL except explicitly specified by order by clause. Hope the above information explains your concern.
Activity
zanmato1984 commentedon Dec 29, 2023
This is an implementation specific behavior, i.e., depending on the underlying hash join algorithm. Currently there are two types of hash join implementation in acero, both having the similar logic. When doing left outer or full outer joins, the matched rows in the left side are first collected, by probing the hash table built from the right side. Then the unmatched rows in the left side are collected. This results in the order you observed. You can check the code below:
For hash join (the normal algorithm), collecting the unmatched rows:
arrow/cpp/src/arrow/acero/hash_join.cc
Lines 440 to 445 in bcaeaa8
For swiss join (a variation of hash join), first collecting the matched rows:
arrow/cpp/src/arrow/acero/swiss_join.cc
Lines 1964 to 1998 in bcaeaa8
then collecting the unmatched rows:
arrow/cpp/src/arrow/acero/swiss_join.cc
Lines 2005 to 2025 in bcaeaa8
Yes, you shouldn't make any assumption of ordering in SQL except explicitly specified by
order byclause. Hope the above information explains your concern.thisisnic commentedon Sep 28, 2026
Closing as appears to not be a bug