You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "0x26res (via GitHub)" <gi...@apache.org> on 2023/09/04 11:09:58 UTC
[GitHub] [arrow] 0x26res opened a new issue, #37542: Left join doesn't preserve order of left for right missing values
0x26res opened a new issue, #37542:
URL: https://github.com/apache/arrow/issues/37542
### Describe the bug, including details regarding any error messages, version, and platform.
I'm trying to do a `left outer` join (the default).
When all the keys in left can be found in right, the order of the left table is preserved:
```python
import pyarrow as pa
left = pa.table(
{
"key": ["KEY_1", "KEY_3"],
"left_value": ["1", "3"],
}
)
right = pa.table(
{
"key": ["KEY_1", "KEY_3"],
"right_value": ["1", "3"],
}
)
```
But if any key from left is missing in right, the order is changed. The missing values are put at the end:
```python
both = left.join(right, keys=["key"])
assert both["key"] == left["key"]
left = pa.table(
{
"key": ["KEY_1", "KEY_2", "KEY_3"],
"left_value": ["1", "2", "3"],
}
)
right = pa.table(
{
"key": ["KEY_1", "KEY_3"],
"right_value": ["1", "3"],
}
)
both = left.join(right, keys=["key"])
assert both["key"].to_pylist() == ["KEY_1", "KEY_3", "KEY_2"], "Should be 1,2,3"
```
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.
Tested with `pyarrow==13.0.0`
### Component(s)
Python
--
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: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Left join doesn't preserve order of left for right missing values [arrow]
Posted by "thisisnic (via GitHub)" <gi...@apache.org>.
thisisnic commented on issue #37542:
URL: https://github.com/apache/arrow/issues/37542#issuecomment-5867526424
Closing as appears to not be a bug
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Left join doesn't preserve order of left for right missing values [arrow]
Posted by "thisisnic (via GitHub)" <gi...@apache.org>.
thisisnic closed issue #37542: Left join doesn't preserve order of left for right missing values
URL: https://github.com/apache/arrow/issues/37542
--
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: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Left join doesn't preserve order of left for right missing values [arrow]
Posted by "zanmato1984 (via GitHub)" <gi...@apache.org>.
zanmato1984 commented on issue #37542:
URL: https://github.com/apache/arrow/issues/37542#issuecomment-1871692692
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:
https://github.com/apache/arrow/blob/bcaeaa8c2d970b81249cfba019475598e3d3109f/cpp/src/arrow/acero/hash_join.cc#L440-L445
For swiss join (a variation of hash join), first collecting the matched rows:
https://github.com/apache/arrow/blob/bcaeaa8c2d970b81249cfba019475598e3d3109f/cpp/src/arrow/acero/swiss_join.cc#L1964-L1998
then collecting the unmatched rows:
https://github.com/apache/arrow/blob/bcaeaa8c2d970b81249cfba019475598e3d3109f/cpp/src/arrow/acero/swiss_join.cc#L2005-L2025
> 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.
--
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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org