LEFT JOIN with FixedSizeBinary join keys returns incorrect values instead of NULL #19067
Description
Describe the bug
When performing a LEFT JOIN with FixedSizeBinary columns as join keys, DataFusion incorrectly populates the join key columns from the right table with values from other rows instead of NULL when there is no match.
Environment
- DataFusion Version: 51 (but not new breakage as this also fails in 47)
- Arrow Version: 57.1.0
To Reproduce
use std::sync::Arc;
use arrow::array::{ArrayRef, FixedSizeBinaryArray, Int64Array, RecordBatch};
use arrow::datatypes::{DataType, Field, Schema};
use datafusion::prelude::*;
#[tokio::test]
async fn test_left_join_fixed_size_binary_bug() {
let ctx = SessionContext::new();
// Left table: 3 rows
let left_schema = Arc::new(Schema::new(vec![
Field::new("id", DataType::Int64, false),
Field::new("join_key", DataType::FixedSizeBinary(4), false),
]));
let left_id = Arc::new(Int64Array::from(vec![1, 2, 3])) as ArrayRef;
let left_join_key = Arc::new(
FixedSizeBinaryArray::try_from_sparse_iter_with_size(
vec![
Some(vec![0xAA, 0xAA, 0xAA, 0xAA]),
Some(vec![0xBB, 0xBB, 0xBB, 0xBB]),
Some(vec![0xCC, 0xCC, 0xCC, 0xCC]), // No match in right table
]
.into_iter(),
4,
)
.unwrap(),
) as ArrayRef;
let left_batch = RecordBatch::try_new(
left_schema.clone(),
vec![left_id, left_join_key]
).unwrap();
// Right table: only 2 rows (no 0xCC)
let right_schema = Arc::new(Schema::new(vec![
Field::new("join_key", DataType::FixedSizeBinary(4), false),
Field::new("value", DataType::Int64, false),
]));
let right_join_key = Arc::new(
FixedSizeBinaryArray::try_from_sparse_iter_with_size(
vec![
Some(vec![0xAA, 0xAA, 0xAA, 0xAA]),
Some(vec![0xBB, 0xBB, 0xBB, 0xBB]),
]
.into_iter(),
4,
)
.unwrap(),
) as ArrayRef;
let right_value = Arc::new(Int64Array::from(vec![1000, 2000])) as ArrayRef;
let right_batch = RecordBatch::try_new(
right_schema.clone(),
vec![right_join_key, right_value]
).unwrap();
ctx.register_batch("left_table", left_batch).unwrap();
ctx.register_batch("right_table", right_batch).unwrap();
let df = ctx.sql(
"SELECT l.id, l.join_key as left_key, r.join_key as right_key, r.value \
FROM left_table l \
LEFT JOIN right_table r ON l.join_key = r.join_key \
ORDER BY l.id"
).await.unwrap();
let results = df.collect().await.unwrap();
// Print results
arrow::util::pretty::print_batches(&results).unwrap();
}Expected behavior
Expected Behavior
+----+-----------+------------+-------+
| id | left_key | right_key | value |
+----+-----------+------------+-------+
| 1 | aaaaaaaa | aaaaaaaa | 1000 |
| 2 | bbbbbbbb | bbbbbbbb | 2000 |
| 3 | cccccccc | NULL | NULL | ← right_key should be NULL
+----+-----------+------------+-------+
Actual Behavior
+----+-----------+------------+-------+
| id | left_key | right_key | value |
+----+-----------+------------+-------+
| 1 | aaaaaaaa | aaaaaaaa | 1000 |
| 2 | bbbbbbbb | bbbbbbbb | 2000 |
| 3 | cccccccc | aaaaaaaa | NULL | ← right_key shows 0xAA instead of NULL!
+----+-----------+------------+-------+
Issue: Row 3 has no match in the right table, so right_key should be NULL, but it shows 0xAA (the value from row 1).
Additional context
Additional Observations
1. IS NULL check returns false incorrectly
SELECT l.id, r.join_key IS NULL as is_null, r.value IS NULL as value_is_null
FROM left_table l
LEFT JOIN right_table r ON l.join_key = r.join_keyResults:
+----+---------+----------------+
| id | is_null | value_is_null |
+----+---------+----------------+
| 1 | false | false |
| 2 | false | false |
| 3 | false | true | ← is_null should be true!
+----+---------+----------------+
The join key column reports as non-NULL when it should be NULL.
2. Bug is specific to join key columns
Non-join FixedSizeBinary columns from the right table correctly return NULL:
SELECT l.id, r.other_binary_column, r.join_key
FROM left_table l
LEFT JOIN right_table r ON l.id = r.id -- Int64 join keyWhen joining on Int64 and selecting a FixedSizeBinary column from the right table, the FixedSizeBinary column is correctly NULL for non-matching rows.
3. Bug is specific to FixedSizeBinary type
The same LEFT JOIN pattern with Int64 join keys works correctly:
SELECT l.id, r.int_join_key
FROM left_table l
LEFT JOIN right_table r ON l.int_join_key = r.int_join_keyInt64 join key columns are correctly NULL for non-matching rows.
Workaround
Use a CASE WHEN expression to explicitly handle NULL values:
SELECT
l.id,
CASE WHEN r.value IS NULL THEN NULL ELSE r.join_key END as right_key_fixed,
r.value
FROM left_table l
LEFT JOIN right_table r ON l.join_key = r.join_keyThis produces correct results, with NULL for right_key_fixed when there's no match. Though with datafusion 51 and arrow 57.1 the work around no longer works and results in a range end index 4 out of range for slice of length 0 in fixed_binary.rs:32:45. I've not yet investigated why the work around breaks in the latest version.
Root Cause Hypothesis
The bug appears to be in the hash join implementation's projection logic for FixedSizeBinary join key columns when handling non-matching rows in a LEFT JOIN. It seems to reuse buffers or dictionary indices incorrectly, causing values from other rows to appear instead of NULL.
The issue is specific to:
- LEFT JOIN operations (not INNER JOIN)
- FixedSizeBinary data types used as join keys
- Join key columns from the right table (non-join columns work correctly)
Impact
This bug affects any LEFT JOIN query where:
- FixedSizeBinary columns are used in the join condition
- The query needs to read join key column values from the right table
- There are non-matching rows
This is particularly problematic for queries that need to check if a join succeeded by examining the join key values themselves.