Incorrect results: hash join silently drops rows when the build side spills to disk

What happens?

A hash join whose build side spills to disk silently returns a strict subset of its rows. With a 60 MB memory limit, a semi join that must return 3,000,000 rows returns 480,000 — no error, no warning. The join key values that survive are exactly those whose partitions were built in the first round of the external build; every row of the remaining keys is discarded by a Bloom filter that was pushed into the probe-side scan before the later rounds ran.

INNER, SEMI, EXISTS and IN (subquery) are all affected. Released 1.5.5 is correct.

To Reproduce

SET threads=1;

-- 3,000,000 rows, 25 distinct keys, 120,000 rows per key.
CREATE TABLE t AS SELECT (i%25)::int AS a FROM range(3000000) tbl(i);
CREATE TABLE u AS SELECT (i%25)::int AS b FROM range(3000000) tbl(i);

-- Any limit small enough that the build side does not fit; the join then goes external.
SET memory_limit='60MB';

SELECT count(*) FROM t WHERE a IN (SELECT b FROM u);

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│       480000 │
└──────────────┘

SET disabled_optimizers='join_filter_pushdown';
SELECT count(*) FROM t WHERE a IN (SELECT b FROM u);

┌────────────────┐
│  count_star()  │
│     int64      │
├────────────────┤
│    3000000     │
│ (3.00 million) │
└────────────────┘

Every row of t has 120,000 matches in u, so the semi join must return all 3,000,000 rows of t.

OS:

Ubuntu 22.04.2 LTS, x86_64, kernel 5.15.0-187-generic

DuckDB Version:

main, commit 8bc48eb3fe36f13b5e57e5e136c3ba85cc7d74d4

DuckDB Client:

CLI

Hardware:

Intel Xeon Silver 4114, 40 cores, 187 GB RAM

Full Name:

Ke Han

Affiliation:

Purdue University

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Yes