bug: correlated EXISTS with cross-type join keys (INT = DECIMAL) wrongly matches NULL probe keys — and returns an unstable number of rows #20364

Search before asking

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

Version

• 8.0.90-v1.2.925-patch-9-6dc65e9df8 (rust-1.94.0-nightly-2026-08-20) — the latest patch release as of 2026-08-21.
• 8.0.90-v1.2.881-ca29960f5c — the original finding comes from a fuzzing campaign on that build.
• 8.0.90-v1.2.935-nightly-43b24ec040 (rust nightly 2026-08-23) — still reproduces (all matrix cells identical), so the bug is neither fixed on the latest nightly nor a recent regression.

What's Wrong?

This is closely related to (and extends) the previously-reported #20351 (LEFT SEMI join treats a NULL probe key as the column type's default value), but that report concluded INT probe keys were unaffected — this report shows the defect also hits INT/BIGINT probe keys whenever the join key requires a cross-type coercion, and adds a second symptom: the number of returned rows is unstable (data-dependent under-count).

A correlated EXISTS whose join key compares columns of different numeric types (e.g. b.decimal_col = a.int_col) is decorrelated into a LEFT SEMI hash join. On that path:

1. A NULL probe key is treated as the (cast) target type's default value 0 and wrongly matches a build-side 0 — EXISTS incorrectly returns true for rows whose correlation column is NULL.
2. The number of wrongly-returned rows is unstable: with three NULL probe rows all of them should be returned if the NULL keys match, but only one is; with two NULL probe rows and no other data, both are returned. Plain EXISTS must never deduplicate probe rows, so the count itself is also wrong.

The identical predicate evaluated as a scalar in a derived-table projection (the logically-equivalent rewritten form) is evaluated correctly, so the defect is specific to the semi-join execution path.

NOT EXISTS is affected in the opposite direction (rows wrongly dropped), so the bug can both add and remove rows depending on predicate polarity.

How to Reproduce?

CREATE TABLE a(c1 BIGINT NULL);
CREATE TABLE b(c0 DECIMAL(20, 0) NULL);
INSERT INTO a VALUES (NULL), (NULL);
INSERT INTO b VALUES (0);

-- Query: returns 2 rows (both NULL rows)  <- WRONG (no value of a matches b)
SELECT a.c1 FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.c0 = a.c1);

-- Equivalent scalar form: returns 0 rows  <- CORRECT
SELECT ref0 FROM (
    SELECT a.c1 AS ref0, EXISTS(SELECT 1 FROM b WHERE b.c0 = a.c1) AS ref1 FROM a
) s WHERE ref1;

With three NULL probe rows plus two non-matching values, the count becomes 1 instead of 0 (or 3) — showing the unstable row count:

CREATE TABLE a2(c1 BIGINT NULL);
INSERT INTO a2 VALUES (NULL), (NULL), (NULL), (2097735747), (2109914711);

SELECT count(*) FROM a2 WHERE EXISTS (SELECT 1 FROM b WHERE b.c0 = a2.c1);
-- returns 1   <- WRONG (expected 0; note it is neither 0 nor 3)

Verified behavior matrix (all on v1.2.925-patch-9, single node):

Cluster information: single node, local datafuselabs/databend Docker container (MySQL protocol). No special configuration. Found by an automated equivalence-testing fuzzer (SQLancer-derived) that compares a query against a logically-equivalent rewrite.

Relationship to open PR #20354

PR #20354 ("preserve null validity in correlated joins") removes the check_for_eliminate_valids short-circuit so the NULL-validity bitmap (build_valids_by_keys) is always computed from the actual key data — which should, in principle, also cover keys that pass through a cross-type coercion, since CAST(NULL AS DECIMAL) stays NULL at runtime. However:

1. The PR is still open (not merged into main as of the v1.2.935-nightly build of 2026-08-23), and the bug fully reproduces on that nightly — so it cannot have been fixed by the shipped code.
2. The PR's new regression tests only cover same-type join keys (DECIMAL(6,0), BOOLEAN, TIMESTAMP columns on both sides); there is no cross-type (INT = DECIMAL) case in the test suite.
3. This report additionally documents a symptom the valids fix alone may not explain: the unstable output cardinality (3 NULL probe rows + 2 non-matching values → only 1 row returned; 2 NULL rows alone → 2 rows), which suggests the semi join also mishandles probe-row multiplicity after the wrong NULL match.

Are you willing to submit PR?

• Yes I am willing to submit a PR!