bug: any spilling hash join fails with Internal 1001 "The level N hybrid hash state has been destroyed" once max_hash_join_spill_level >= 2

Search before asking

• I had searched in the issues and found no similar issues.

Version

8.0.90-v1.2.943-nightly-419338dde9(rust-1.94.0-nightly-2026-09-15T17:38:02Z)

(datafuselabs/databend:nightly, pulled 2026-09-16)

Also reproduced on released v1.2.881 (8.0.90-v1.2.881-ca29960f5c, 2026-04-17) with a higher trigger threshold — see "Affected versions" below.

Source inspected at 6b3117020f72273b79931808e90dd74c50ea3c7e (2026-09-16); the defect is still present at HEAD.

What's Wrong?

max_hash_join_spill_level is documented as

Maximum recursion depth for the hash join spill. Each recursion level repartition data into 16 smaller parts to ensure it fits in memory.

i.e. it is a robustness knob: raising it should let a join survive build sides that a single round of repartitioning cannot fit in memory. It has range 0..=16 and defaults to 1.

Instead, on current nightly, raising it to anything >= 2 makes every hash join that actually spills fail outright:

ERROR 1105 (HY000): Internal. Code: 1001,
Text = Error state: The level 2 hybrid hash state has been destroyed.

The query returns no result at all. It is not a wrong-result bug — it fails loudly — but the setting that is supposed to make large joins more robust currently breaks them, and the default (1) is only one step away from the broken range.

The failure does not require deep recursion: it fires on a 3-row join whose level-1 partitions obviously fit in memory. It needs only that the join takes the spill path at all.

Affected join types (all with the join actually spilling):

Other observations:

• enable_experimental_new_join = 0 (old hash join) is not affected.
• max_threads = 1 and max_threads = 4 both fail.
• 100% deterministic — every execution fails; the = 1 arm never does.
• Not persistent: after a level-2 query fails, a level-1 query in the same session still succeeds. The bad state lives for the duration of one query.
• The level named in the message is the recursion depth actually reached, not the value of the setting (cap 3 under natural spill reports level 3; cap 3, 8 and 16 on a forced-spill 3-row join all report level 2).

How to Reproduce?

A. Minimal, 3 rows (uses the test-only force_join_data_spill)

CREATE OR REPLACE TABLE hj_a (k INT, v INT);
CREATE OR REPLACE TABLE hj_b (k INT, w INT);
INSERT INTO hj_a VALUES (1,10),(2,20),(3,30);
INSERT INTO hj_b VALUES (1,100),(2,200),(4,400);

SET force_join_data_spill = 1;      -- make the tiny join take the spill path

SET max_hash_join_spill_level = 1;  -- default
SELECT a.k, a.v, b.w FROM hj_a a JOIN hj_b b ON a.k = b.k ORDER BY a.k;
-- (1,10,100), (2,20,200)                                            correct

SET max_hash_join_spill_level = 2;
SELECT a.k, a.v, b.w FROM hj_a a JOIN hj_b b ON a.k = b.k ORDER BY a.k;
-- ERROR 1105 (HY000): Internal. Code: 1001,
--   Text = Error state: The level 2 hybrid hash state has been destroyed.

Server used for the above:

docker run -d --name databend -p 3307:3307 \
  -e QUERY_DEFAULT_USER=root -e QUERY_DEFAULT_PASSWORD=root \
  -e QUERY_STORAGE_TYPE=fs datafuselabs/databend:nightly

B. No test-only flag — natural spill under a memory limit

This is the shape a real deployment hits. No force_join_data_spill; the join spills because of the memory limit.

SET max_threads = 4;
CREATE OR REPLACE TABLE jbig   AS SELECT number AS id, ((number * 7919)   % 1000003) AS b,
       to_string(number % 5000) AS c FROM numbers(3000000);
CREATE OR REPLACE TABLE jsmall AS SELECT number AS id, ((number * 104729) % 1000003) AS b,
       to_string(number % 777)  AS c FROM numbers(1500000);

SET max_memory_usage       = 67108864;   -- 64 MB
SET max_query_memory_usage = 67108864;

SET max_hash_join_spill_level = 1;
SELECT count(*), sum(j0.b), sum(j1.id) FROM jbig j0 JOIN jsmall j1 ON j0.b = j1.b;
-- 4499987  2249991777741  3374989389583                              correct

SET max_hash_join_spill_level = 2;
SELECT count(*), sum(j0.b), sum(j1.id) FROM jbig j0 JOIN jsmall j1 ON j0.b = j1.b;
-- ERROR 1105 (HY000): Internal. Code: 1001,
--   Text = Error state: The level 2 hybrid hash state has been destroyed.

SET max_hash_join_spill_level = 3;
SELECT count(*), sum(j0.b), sum(j1.id) FROM jbig j0 JOIN jsmall j1 ON j0.b = j1.b;
-- ERROR 1105 ... The level 3 hybrid hash state has been destroyed.

EXPLAIN ANALYZE on the = 1 arm confirms the join really spills:

HashJoin
├── join type: INNER
├── numbers remote spilled by write: 128
├── bytes remote spilled by write: 23.06 MiB
├── numbers remote spilled by read: 128

max_memory_usage = max_query_memory_usage = 268435456 with join_spilling_memory_ratio = 5 reproduces identically. At 256 MB with the default ratio of 60 the 1.5M-row build side still fits, so no spill happens and the query succeeds — the spill is what matters, not the limit per se.

What's Expected?

The same rows at every legal value of max_hash_join_spill_level. The setting caps how many times a partition that does not fit in memory may be repartitioned; raising the cap should only ever allow more recursion, never turn a working join into an internal error.

Affected versions

The defect is not new to the current nightly, but the threshold has moved sharply in the wrong direction. Forced spill, 3-row tables, INNER join, varying only max_hash_join_spill_level:

So on the older release you had to push the cap near its maximum before hitting it; on current nightly the very first step above the default fails. Whatever changed between those two builds made the faulty path reachable much earlier.

Root cause (reading of the source at 6b31170)

src/query/service/src/pipelines/processors/transforms/new_hash_join/hash_join_factory.rs keeps three per-query maps of Weak handles, one entry per recursion level:

grace_state:  CStyleCell<HashMap<usize, Weak<GraceHashJoinState>>>,
basic_state:  CStyleCell<HashMap<usize, Weak<BasicHashJoinState>>>,
hybrid_state: CStyleCell<HashMap<usize, Weak<HybridHashJoinState>>>,

All three create_*_state(level) methods have the same shape: on Entry::Occupied they upgrade() the Weak, and if the upgrade fails they return an Internal error rather than recreating the state:

Entry::Occupied(v) => match v.get().upgrade() {
    Some(v) => Ok(v),
    None => Err(ErrorCode::Internal(format!(
        "Error state: The level {} hybrid hash state has been destroyed.", level))),
},

That is only safe if a dropped state removes its own entry. Two of the three do:

• memory/basic_state.rs:92 — impl Drop for BasicHashJoinState → factory.remove_basic_state(self.level)
• grace/grace_state.rs:67 — impl Drop for GraceHashJoinState → factory.remove_grace_state(self.level)

HybridHashJoinState has no Drop impl, and there is no remove_hybrid_state anywhere in src/ (grep -rn "remove_hybrid_state" src/ → no hits). So once a level-N HybridHashJoinState is created and dropped, a dangling Weak stays in hybrid_state, and the next create_hybrid_state(N) in the same query takes the Entry::Occupied branch, fails to upgrade, and errors.

Why the default is immune and >= 2 is not: the top-level join is created at level 0 (physical_hash_join.rs:486 — factory.create_hash_join(self.join_type, 0)), and recursion is gated by

pub fn can_next_layer_join(&self) -> bool { self.level < self.max_level }

with max_level = max_hash_join_spill_level.min(HASH_JOIN_SPILL_MAX_LEVEL) (hybrid/hybrid_state.rs:63-69; the constant is 7 on SSE4.2 targets, 15 otherwise). At the default cap of 1 the deepest hybrid state created is level 1, which stays alive for the query as the sub-join of the level-0 grace join. At cap >= 2, switch_to_grace_mode → create_grace_join → state.create_hybrid_join(...) → create_hybrid_state(level + 1) creates a level-2 state whose holder is transient, and the create/drop/re-create cycle trips the check. (The exact interleaving that drops it — a second switch_to_grace_mode from final_build vs. a second processor — I have not pinned down; it reproduces at max_threads = 1 as well as 4.)

Suggested fix, matching the two sibling states: give HybridHashJoinState a Drop impl that calls a new HashJoinFactory::remove_hybrid_state(self.level). Alternatively, treat a failed upgrade() as "recreate" rather than as an error in all three create_*_state methods, since a dead Weak means nothing is holding the state and a fresh one is what the caller wants.

Consistency note: FULL OUTER and NOT IN are unaffected because they never reach this code — create_grace_join / create_memory_join have _ => unreachable!() arms and those shapes are planned differently.

Are you willing to submit PR?

• Yes I am willing to submit a PR!