Unexpected NULL-Left Rows with RIGHT OUTER JOIN using Cluster Table Functions with global join

[alsugiliazova]

Summary

A RIGHT OUTER JOIN between s3Cluster and icebergS3Cluster table functions with object_storage_cluster_join_mode='global' returns 6 rows instead of the expected 2. The 2 matched rows appear correctly, but 4 additional spurious rows with NULL on the left side are emitted — as if those right-side rows had no match on the left, even though every right-side row does match a left-side row.

---

Test Parameters

Parameter	Value
Left table	s3Cluster(replicated_cluster, 'http://minio:9000/warehouse/data3/data/**.parquet', ...)
Right table	icebergS3Cluster(replicated_cluster, 'http://minio:9000/warehouse/data1', ...)
Join clause	RIGHT OUTER JOIN
Join condition	t1.boolean_col = t2.boolean_col AND t1.string_col = t2.string_col
object_storage_cluster_join_mode	global
object_storage_cluster	replicated_cluster
Format	Values
Order by	tuple(*)

All tables have same two rows:

SELECT *
FROM merge_tree_table_d435968e_3cab_11f1_a333_de7b9eea348f

Query id: 4ed08192-c8f0-4221-82f3-2058f558586c

   ┌─boolean_col─┬─long_col─┬─double_col─┬─string_col─┬──────────────timestamp_col─┬───date_col─┬────time_col─┬────────────timestamptz_col─┬─integer_col─┬─float_col─┬─decimal_col─┐
1. │ true        │     1000 │     456.78 │ Alice      │ 2024-01-01 13:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │        1000 │    456.78 │      456.78 │
2. │ false       │     2000 │     456.78 │ Bob        │ 2024-01-01 13:00:00.000000 │ 2024-01-01 │ 43200000000 │ 2024-01-01 12:00:00.000000 │        2000 │    456.78 │      456.78 │
   └─────────────┴──────────┴────────────┴────────────┴────────────────────────────┴────────────┴─────────────┴────────────────────────────┴─────────────┴───────────┴─────────────┘

2 rows in set. Elapsed: 0.010 sec. 

---

What Happened

Step 1 — Baseline Query (MergeTree tables, passed)

The test first materialized both data sources into local MergeTree tables via
CREATE TABLE ... ENGINE = MergeTree ORDER BY tuple() AS SELECT * FROM <cluster_function>(...)
and ran the same RIGHT OUTER JOIN to establish the expected result. This produced 2 rows — one matched row for Bob and one matched row for Alice, with no NULL-left rows (every right-side row has a match on the left).

Expected output (2 rows):

(false,2000,456.78,'Bob','2024-01-01 12:00:00.000000','2024-01-01','1970-01-01 12:00:00.000000','2024-01-01 12:00:00.000000',2000,456.78,456.78,false,2000,456.78,'Bob','2024-01-01 13:00:00.000000','2024-01-01',43200000000,'2024-01-01 12:00:00.000000',2000,456.78,456.78),
(true,1000,456.78,'Alice','2024-01-01 12:00:00.000000','2024-01-01','1970-01-01 12:00:00.000000','2024-01-01 12:00:00.000000',1000,456.78,456.78,true,1000,456.78,'Alice','2024-01-01 13:00:00.000000','2024-01-01',43200000000,'2024-01-01 12:00:00.000000',1000,456.78,456.78)

Step 2 — Actual Query (cluster table functions, failed)

The same join was then executed using the s3Cluster and icebergS3Cluster table functions directly, with settings:

• object_storage_cluster_join_mode='global'
• object_storage_cluster='replicated_cluster'

This produced 6 rows — the 2 correct matched rows, plus 4 extra rows with NULL values in all left-side columns (2 spurious (NULL, ..., Bob) rows and 2 spurious (NULL, ..., Alice) rows).

Actual output (6 rows):

(false,2000,...,'Bob',...,false,2000,...,'Bob',...),
(true,1000,...,'Alice',...,true,1000,...,'Alice',...),
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,false,2000,...,'Bob',...),
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,false,2000,...,'Bob',...),
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,true,1000,...,'Alice',...),
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,true,1000,...,'Alice',...)

Assertion Failure

assert result.output == expected_result.output

The diff shows the first two rows match, followed by 4 extra NULL-left rows that should not exist.

---

Root Cause Analysis

The RIGHT OUTER JOIN contract guarantees that every right-side row appears exactly once in the result — matched against its corresponding left-side row(s) where matches exist, or padded with NULLs on the left where no match exists globally. The MergeTree baseline confirms this behavior: every right-side row matches a left-side row, so there are no NULL-left rows.

However, when the query is distributed across the cluster via s3Cluster/icebergS3Cluster with object_storage_cluster_join_mode='global', the "outer" side of the join is not evaluated globally:

• Each node processes a partition of the data.
• When a given right-side row does not find a match in the local left-side partition, the node emits a NULL-left row, treating it as "unmatched".
• Those right-side rows do have matches on other nodes, but the distributed join executor does not deduplicate or cross-check matches across nodes before emitting the NULL-padded rows.
• The final result therefore contains both the correctly matched rows and spurious NULL-left rows produced by nodes where the match did not happen locally.

This is a ClickHouse bug in the global join mode for cluster table functions: outer join semantics are applied per-node rather than globally, resulting in spurious NULL-padded rows for right-side rows that are globally matched but locally unmatched.

Why the Test's Non-Stable Handling Did Not Catch This

The test has logic (lines 335–468 in joins.py) that relaxes the output assertion for certain "non-stable" table-type combinations. However, all those exemptions check for object_storage_cluster_join_mode == "allow". This failure occurs with object_storage_cluster_join_mode == "global", which is not covered by the exemptions. The global mode exhibits the same non-stable behavior as allow for this table-type combination, but the test was not updated to account for it.

---

Relationship to Existing Issue

This bug is closely related to Altinity/ClickHouse#1661, which reports the same category of problem (distributed cluster joins producing incorrect row counts with object_storage_cluster_join_mode='global'), but for INNER ANY JOIN — where matched rows are duplicated. This failure is a different manifestation of the same underlying issue: the distributed join executor does not enforce global join semantics across cluster nodes.

Issue	Join Type	Symptom
#1661	INNER ANY JOIN	Matched rows duplicated (6 rows instead of 2)
This failure	RIGHT OUTER JOIN	Matched rows correct, but 4 spurious NULL-left rows added (6 rows instead of 2)

---

Affected Table-Type Combination

Left Table Type	Right Table Type	Join Mode	Join Clause	Status
s3Cluster_table_function	icebergS3Cluster_table_function	allow	any non-stable	Handled (relaxed assertion)
s3Cluster_table_function	icebergS3Cluster_table_function	global	RIGHT OUTER JOIN	NOT handled — this failure

---

Possible Resolutions

Option A — ClickHouse Fix (Preferred)

File a bug (or extend #1661) against ClickHouse for RIGHT OUTER JOIN emitting spurious NULL-left rows when using cluster table functions with object_storage_cluster_join_mode='global'. The outer-join semantics must be enforced globally, not per-node — a right-side row should only produce a NULL-left row if it has no match on any node.

Option B — Test Workaround

Extend the non-stable result handling in check_join() to also cover object_storage_cluster_join_mode == "global" for the s3Cluster + icebergS3Cluster combination (and likely other cluster function pairs), relaxing the assertion to only check exitcode == 0.

Option C — Combined

File the ClickHouse bug and add the test relaxation with an xfail or similar marker referencing the upstream issue, so that the test suite is not blocked while waiting for a fix.

---

Reproduction

Input data (both s3Cluster and icebergS3Cluster return 8 rows each; every right-side row has a matching left-side row on (boolean_col, string_col)).

SELECT *
FROM s3Cluster(replicated_cluster, 'http://minio:9000/warehouse/data3/data/**.parquet', '<user>', '<password>') AS t1
RIGHT OUTER JOIN icebergS3Cluster(replicated_cluster, 'http://minio:9000/warehouse/data1', '<user>', '<password>') AS t2
  ON t1.boolean_col = t2.boolean_col AND t1.string_col = t2.string_col
ORDER BY tuple(*)
SETTINGS object_storage_cluster_join_mode='global', object_storage_cluster='replicated_cluster'
FORMAT Values

Compare against the same query against MergeTree copies of the same data — the MergeTree version returns 2 rows (all matched), while the cluster version returns 6 rows (2 matched + 4 spurious NULL-left rows).

[ianton-ru]

Join over cluster function reuses algorithm of join over distributed tables.
Join over distributed tables has the same issue in upstream.
On upstream version 26.2.4.23:
Cluster 'shards' has three shard, each with one replica.

create table t1 on cluster 'shards' (k1 UInt32, v1 String) engine=MergeTree() order by k1;
create table t2 on cluster 'shards' (k2 UInt32, v2 String) engine=MergeTree() order by k2;
create table d1 on cluster 'shards' as t1 engine=Distributed('shards', default, t1, rand());
create table d2 on cluster 'shards' as t2 engine=Distributed('shards', default, t2, rand());
insert into d1 values (1, 'a'),(2, 'b'),(3, 'c'),(1, 'a'),(2, 'b'),(3, 'c'),(1, 'a'),(2, 'b'),(3, 'c');
insert into d2 values (1, 'A'),(2, 'B'),(3, 'C'),(1, 'A'),(2, 'B'),(3, 'C'),(1, 'A'),(2, 'B'),(3, 'C');
insert into d1 values (4, 'd'),(5, 'e'),(6, 'f'),(4, 'd'),(5, 'e'),(6, 'f'),(4, 'd'),(5, 'e'),(6, 'f');
insert into d2 values (7, 'G'),(8, 'H'),(9, 'I'),(7, 'G'),(8, 'H'),(9, 'I'),(7, 'G'),(8, 'H'),(9, 'I');

# Correct result:
select * from (select * from d1) as d1 right outer join (select * from d2) as d2 on d1.k1=d2.k2 order by all
    ┌─k1─┬─v1─┬─k2─┬─v2─┐
 1. │  0 │    │  7 │ G  │
 2. │  0 │    │  7 │ G  │
 3. │  0 │    │  7 │ G  │
 4. │  0 │    │  8 │ H  │
 5. │  0 │    │  8 │ H  │
 6. │  0 │    │  8 │ H  │
 7. │  0 │    │  9 │ I  │
 8. │  0 │    │  9 │ I  │
 9. │  0 │    │  9 │ I  │
10. │  1 │ a  │  1 │ A  │
11. │  1 │ a  │  1 │ A  │
12. │  1 │ a  │  1 │ A  │
13. │  1 │ a  │  1 │ A  │
14. │  1 │ a  │  1 │ A  │
15. │  1 │ a  │  1 │ A  │
16. │  1 │ a  │  1 │ A  │
17. │  1 │ a  │  1 │ A  │
18. │  1 │ a  │  1 │ A  │
19. │  2 │ b  │  2 │ B  │
20. │  2 │ b  │  2 │ B  │
21. │  2 │ b  │  2 │ B  │
22. │  2 │ b  │  2 │ B  │
23. │  2 │ b  │  2 │ B  │
24. │  2 │ b  │  2 │ B  │
25. │  2 │ b  │  2 │ B  │
26. │  2 │ b  │  2 │ B  │
27. │  2 │ b  │  2 │ B  │
28. │  3 │ c  │  3 │ C  │
29. │  3 │ c  │  3 │ C  │
30. │  3 │ c  │  3 │ C  │
31. │  3 │ c  │  3 │ C  │
32. │  3 │ c  │  3 │ C  │
33. │  3 │ c  │  3 │ C  │
34. │  3 │ c  │  3 │ C  │
35. │  3 │ c  │  3 │ C  │
36. │  3 │ c  │  3 │ C  │
    └────┴────┴────┴────┘

# Broken result:
select * from d1 right outer join d2 on d1.k1=d2.k2 order by all settings distributed_product_mode='global'
    ┌─k1─┬─v1─┬─k2─┬─v2─┐
 1. │  0 │    │  3 │ C  │
 2. │  0 │    │  3 │ C  │
 3. │  0 │    │  3 │ C  │
 4. │  0 │    │  7 │ G  │
 5. │  0 │    │  7 │ G  │
 6. │  0 │    │  7 │ G  │
 7. │  0 │    │  7 │ G  │
 8. │  0 │    │  7 │ G  │
 9. │  0 │    │  7 │ G  │
10. │  0 │    │  7 │ G  │
11. │  0 │    │  7 │ G  │
12. │  0 │    │  7 │ G  │
13. │  0 │    │  8 │ H  │
14. │  0 │    │  8 │ H  │
15. │  0 │    │  8 │ H  │
16. │  0 │    │  8 │ H  │
17. │  0 │    │  8 │ H  │
18. │  0 │    │  8 │ H  │
19. │  0 │    │  8 │ H  │
20. │  0 │    │  8 │ H  │
21. │  0 │    │  8 │ H  │
22. │  0 │    │  9 │ I  │
23. │  0 │    │  9 │ I  │
24. │  0 │    │  9 │ I  │
25. │  0 │    │  9 │ I  │
26. │  0 │    │  9 │ I  │
27. │  0 │    │  9 │ I  │
28. │  0 │    │  9 │ I  │
29. │  0 │    │  9 │ I  │
30. │  0 │    │  9 │ I  │
31. │  1 │ a  │  1 │ A  │
32. │  1 │ a  │  1 │ A  │
33. │  1 │ a  │  1 │ A  │
34. │  1 │ a  │  1 │ A  │
35. │  1 │ a  │  1 │ A  │
36. │  1 │ a  │  1 │ A  │
37. │  1 │ a  │  1 │ A  │
38. │  1 │ a  │  1 │ A  │
39. │  1 │ a  │  1 │ A  │
40. │  2 │ b  │  2 │ B  │
41. │  2 │ b  │  2 │ B  │
42. │  2 │ b  │  2 │ B  │
43. │  2 │ b  │  2 │ B  │
44. │  2 │ b  │  2 │ B  │
45. │  2 │ b  │  2 │ B  │
46. │  2 │ b  │  2 │ B  │
47. │  2 │ b  │  2 │ B  │
48. │  2 │ b  │  2 │ B  │
49. │  3 │ c  │  3 │ C  │
50. │  3 │ c  │  3 │ C  │
51. │  3 │ c  │  3 │ C  │
52. │  3 │ c  │  3 │ C  │
53. │  3 │ c  │  3 │ C  │
54. │  3 │ c  │  3 │ C  │
55. │  3 │ c  │  3 │ C  │
56. │  3 │ c  │  3 │ C  │
57. │  3 │ c  │  3 │ C  │
    └─k1─┴─v1─┴─k2─┴─v2─┘

[ianton-ru]

May be related to ClickHouse#49771