Repository navigation
Estimate equality to a scalar subquery without a blanket 20% fallback #25623
Description
Describe the bug
Replacing a month-key literal with an equivalent scalar subquery changes the
filter estimate from 31 to 14,610 rows. Both return the same 29 rows.
To Reproduce
From the repository root, with
PR #25570 applied:
cargo build --profile ci --locked -p datafusion-benchmarks --bin dfbench
repro_dir=$(mktemp -d)
mkdir -p "$repro_dir/data"
curl --fail --location \
https://raw.githubusercontent.com/apache/datafusion-benchmarks/32f67477f692453616d2fa98a05a37c5eb4cae49/tpcds/data/sf1/date_dim.parquet \
-o "$repro_dir/data/date_dim.parquet"
cat > "$repro_dir/repro.sql" <<'SQL'
SET datafusion.execution.target_partitions = 1;
SET datafusion.optimizer.enable_dynamic_filter_pushdown = false;
SELECT d_date_sk FROM date_dim WHERE d_month_seq = 1201;
SELECT d_date_sk FROM date_dim
WHERE d_month_seq = (
SELECT MAX(d_month_seq) FROM date_dim WHERE d_year = 2000 AND d_moy = 2
);
SQL
target/ci/dfbench statistics \
--path "$repro_dir/data" --query_path "$repro_dir/repro.sql"Observed at
this revision
with the pinned SF1 data above. Inspect the SELECT reports in order; ignore the
empty SET reports.
| Predicate | FilterExec node | Estimated rows | Actual rows |
|---|---|---|---|
| Literal equality | 0 |
31 | 29 |
| Scalar-subquery equality | 0.0 |
14,610 | 29 |
Expected behavior
When the scalar value is unavailable during planning, use available
equality-selectivity information, such as the left-hand NDV or a suitable
range-based estimate, instead of an unconditional 20%. This should not require
executing arbitrary subqueries during planning.
Additional context
Reduced from the scalar month lookup in TPC-DS Q6; MAX replaces its DISTINCT
lookup to keep the reproducer scalar by construction. The outer input estimate
is correct at 73,049 rows. This is an estimation-policy improvement, not a
query-result correctness bug.
Part of #25610.
Activity
mohitgurav20 commentedon Sep 24, 2026
Hi @gabotechs, thanks for opening this!
This is a great cardinality estimation refinement. When scalar subqueries are used in equality predicates (e.g. col = (SELECT ...)), the planner currently falls back to a blanket 20% selectivity because the actual scalar value isn't available during planning. As you pointed out, we should instead fall back to the available column statistics—specifically, using 1.0 / NDV (Number of Distinct Values) for equality predicates, which provides a much more accurate estimate than an arbitrary 20%.
I've recently been working on optimizing the physical execution layer (specifically memory and buffer tracking in HashJoinExec) and have been spending a lot of time with DataFusion's physical expressions and statistics, so I have the exact context needed to implement this correctly.
I plan to update the selectivity estimation logic for binary expressions so that when one side is an unresolved scalar subquery (or any unknown scalar), we gracefully fall back to utilizing the other column's NDV from the available statistics instead of defaulting to 20%.
Could you assign this to me?
asolimando commentedon Sep 24, 2026
@mohitgurav20 the proposed plan makes sense, you can just reply with "take" and the bot will assign the issue to you, I can help with reviews although I don't have write permission, but that usually help towards getting things merged
mohitgurav20 commentedon Sep 24, 2026
thank you sir for your kind reply ! i'll definetly work on it and make a pr
mohitgurav20 commentedon Sep 24, 2026
take
mohitgurav20 commentedon Sep 24, 2026
sir it would be help full if i get a msg of assigned so that i can go on and work on it
asolimando commentedon Sep 24, 2026
I think the bot is just being slow, that's usually the way to self-assign tickets for contributors
mohitgurav20 commentedon Sep 25, 2026
sir still i haven't got a msg of being assigned i have a pr draft i have worked on it if u permit i can make pr and u can review the pr ..!
asolimando commentedon Sep 25, 2026
Nobody has claimed the issue so I think it's safe to go ahead. You can retry the "take" message, maybe the second message interfered with the bot. It might be worth sending a message to the dev list to raise awareness of the problem
mohitgurav20 commentedon Sep 25, 2026
Thank you for the reply sirr.
mohitgurav20 commentedon Sep 25, 2026
take