I hereby agree to the terms of the CLA available at: https://docs.databend.com/dev/policies/cla/

Motivation

Do not apply a join runtime filter below LIMIT, a window, or a scalar-subquery check. Removing rows there can change the answer or hide a required error.

Two small reproductions

Measured on a one-node OSS server: old base 5c09ab04fdc5, fixed code 5cee5dafcbb5. Each result below was observed three times. Run each example in a fresh session/schema; runtime filters are enabled by default.

1. LIMIT: expected no rows, old version returns 1

CREATE TABLE t(k INT);
CREATE TABLE keep_t(k INT);
INSERT INTO t VALUES (0);
INSERT INTO t VALUES (1);
INSERT INTO keep_t VALUES (1);

SELECT q.k
FROM (SELECT k FROM t ORDER BY k LIMIT 1) q
JOIN keep_t j ON q.k = j.k;

The inner query must select 0 first. It cannot match keep_t, so the result must be empty.

The old RF removes the block containing 0 before LIMIT, allowing 1 to become the first row. The fix prevents that pushdown.

No SET is required here. Both join-reorder settings were tested. Keep the two separate INSERTs: they create separate blocks, allowing the old RF to prune one. With both values in one block, this tiny case did not show the wrong result.

2. Scalar subquery: expected an error, old version returns 2 30

SET disable_join_reorder = 1;
CREATE TABLE a(k INT);
CREATE TABLE b(k INT, v INT);
CREATE TABLE keep_a(k INT);
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO b VALUES (1,10), (1,20), (2,30);
INSERT INTO keep_a VALUES (0), (2);

SELECT q.k, q.v
FROM (
  SELECT a.k, (SELECT b.v FROM b WHERE b.k = a.k) AS v
  FROM a
) q
JOIN keep_a c ON q.k = c.k
WHERE q.v IS NOT NULL;

For this plan, key 1 has two scalar values. RF off reports:

1001: Scalar subquery can't return more than one row, but got 2

The old parent RF removes key 1 before the scalar check, hiding the error. Although that intermediate join is labelled Inner, it still enforces scalar cardinality. The fix treats its single_to_inner marker as a barrier.

Why the SET: this small example needs disable_join_reorder = 1 to keep q on the parent's probe side. With the default reordering, the measured plan swaps the sides; the RF targets keep_a instead, and even the old version reports the error. This SET exposes the bad path; it is not a requirement for the defect in general. The separate INSERTs into a again allow block pruning.

enable_join_runtime_filter = 1 is already the default, so neither example needs that SET. Setting it to 0 is only the control shown in the tables, not a reproduction requirement.

Test coverage

• Row-sensitive SQL: a two-row fixture checks LIMIT, window count and an expression join key with RF off/on. Separate INSERTs allow the old RF to prune a block and expose the incorrect result.
• Scalar SQL: two input keys and three scalar rows check that RF preserves the duplicate-row error. Comments explain the block layout, keep-side key 0 and required join-reorder setting.
• Rust: three deterministic tests check operator barriers, safe versus blocked equality endpoints, and converted-Single markers. They assert target selection directly, including that safe targets remain enabled—properties not established by correct SQL results alone.
• Expected plans: CSE/CTE/self-join plans exclude unsupported materialized-CTE targets; virtual-column/TPC-DS plans exclude unsafe extra endpoints. The five golden files change only RF annotations, not SQL, estimates or timing placeholders.

Tests

• Unit Test

Both SQL files fail at the intended assertions on the old version and pass over HTTP/MySQL on the fix. The complete RF module (7 tests), query all-targets clippy and formatting checks pass.

Type of change

• Bug Fix (non-breaking change which fixes an issue)

AI assistance

• AI usage: AI coding agents investigated the physical RF paths, drafted the scoped checks and synthetic regressions, and assisted validation and privacy review.
• Responsible human: @dantengsky
• The responsible human has read every line of this diff and can explain each change

This change is