Inner joins are incorrect

[ravirahman]

Describe the bug, including details regarding any error messages, version, and platform.

I am running into an issue where inner joins between two tables are incorrectly computed. To reproduce this issue, here is a zip file containing two example tables (table_1.parquet and table_2.parquet). I have encountered this problem on pyarrow 10.0.0 - 13.0.0:

The schema for table_1 is:

col_1: timestamp[us, tz=UTC]  # All values are the same timestamp ("2023-09-14 21:43:18.678917")
col_2: large_string  
col_3: large_string  # All values are the same ("foo")

And for table_2:

col_1: timestamp[us, tz=UTC]  # All values are the same timestamp ("2023-09-14 21:43:18.678917")
col_2: large_string
col_3: large_string  # All values are the same ("foo")
col_4: large_string  # All values are the same ("bar")

Below is an example to reproduce the issue I am running in to with inner joins:

import pyarrow.parquet as pq

table_1 = pq.read_table("table_1.parquet")
table_2 = pq.read_table("table_2.parquet")

inner_joined = table_1.join(table_2, ["col_1", "col_2", "col_3"], join_type="inner",)
print(len(inner_joined))  # prints 3596 (incorrect)

inner_joined = table_2.join(table_1, ["col_1", "col_2", "col_3"], join_type="inner", use_threads=False)
print(len(inner_joined))  # prints 3601 (incorrect). "A inner join B" should be the same as "B inner join A"?

outer_joined = table_1.join(table_2, ["col_1", "col_2", "col_3"], join_type="left outer")
print(len(outer_joined))  # prints 6289 (correct)

# Table 2 is a superset of table 1. Thus, the null count for `col_4` when doing a left join should be zero
print(outer_joined.column("col_4").null_count)  # prints 0 (correct)

I believe this is a bug but please let me know if I am missing anything.

Component(s)

Python

[emarx]

Hi all! Following up here -- this is a very significant bug. We've stopped using inner joins (using left joins and drop nulls instead) as inner joins return incorrect rows. Recommend anyone coming across this GitHub issue do the same until this is remediated.

[AlenkaF]

I tried the code locally on dev (15.0.0.dev) and wasn't able to reproduce:

In [1]: import pyarrow.parquet as pq
   ...: 
   ...: table_1 = pq.read_table("python/table_1.parquet")
   ...: table_2 = pq.read_table("python/table_2.parquet")
   ...: 
   ...: inner_joined = table_1.join(table_2, ["col_1", "col_2", "col_3"], join_type="inner",)
   ...: print(len(inner_joined))
   ...: 
   ...: inner_joined = table_2.join(table_1, ["col_1", "col_2", "col_3"], join_type="inner", use_threads=False)
   ...: print(len(inner_joined))
   ...: 
   ...: outer_joined = table_1.join(table_2, ["col_1", "col_2", "col_3"], join_type="left outer")
   ...: print(len(outer_joined))
   ...: 
   ...: print(outer_joined.column("col_4").null_count)

6289
6289
6289
0

but when running the same code on pyarrow version 13.0.0 I get the reported behaviour:

>>> import pyarrow as pa
>>> pa.__version__
'13.0.0'

>>> import pyarrow.parquet as pq
>>> table_1 = pq.read_table("../repos/arrow/python/table_1.parquet")
>>> table_2 = pq.read_table("../repos/arrow/python/table_2.parquet")

>>> inner_joined = table_1.join(table_2, ["col_1", "col_2", "col_3"], join_type="inner",)
>>> print(len(inner_joined))
3596

>>> inner_joined = table_2.join(table_1, ["col_1", "col_2", "col_3"], join_type="inner", use_threads=False)
>>> print(len(inner_joined))
3601

>>> outer_joined = table_1.join(table_2, ["col_1", "col_2", "col_3"], join_type="left outer")
>>> print(len(outer_joined))
6289

>>> print(outer_joined.column("col_4").null_count)
0

I will try to see which PR fixed this issue.

[AlenkaF]

Missed the issue and the PR already linked in this report. The fix for the bug is:

• [C++][Acero] Incorrect results in inner join #38074
• GH-38074: [C++] Fix Offset Size Calculation for Slicing Large String and Binary Types in Hash Join #38147

and was added in the 14.0.0 release.