Describe the bug
Multi-value IN and NOT IN predicates fall back to 20%, regardless of list
coverage. Even including or excluding the entire month domain produces the same
estimate.
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_moy IN (1, 2, 3);
SELECT d_date_sk FROM date_dim WHERE d_moy BETWEEN 1 AND 3;
SELECT d_date_sk FROM date_dim WHERE d_moy NOT IN (1, 2, 3);
SELECT d_date_sk FROM date_dim
WHERE d_moy IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
SELECT d_date_sk FROM date_dim
WHERE d_moy NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
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 |
| IN (1, 2, 3) |
0 |
14,610 |
18,049 |
| BETWEEN 1 AND 3 |
0 |
18,263 |
18,049 |
| NOT IN (1, 2, 3) |
0 |
14,610 |
55,000 |
| IN (all 12 months) |
0 |
14,610 |
73,049 |
| NOT IN (all 12 months) |
0 |
14,610 |
0 |
Expected behavior
Use list membership, available bounds/NDVs, and null semantics to estimate
selectivity. On this non-null integer domain, including every possible month
should retain all rows; excluding every month should retain none.
Additional context
Isolates the membership predicates used in TPC-DS Q18/Q27 on a small numeric
domain. This is concrete evidence for the InList work already described in
#14237, suitable for a
focused sub-issue or a comment there.
Part of #25610.
Describe the bug
Multi-value IN and NOT IN predicates fall back to 20%, regardless of list
coverage. Even including or excluding the entire month domain produces the same
estimate.
To Reproduce
From the repository root, with
PR #25570 applied:
Observed at
this revision
with the pinned SF1 data above. Inspect the SELECT reports in order; ignore the
empty SET reports.
00000Expected behavior
Use list membership, available bounds/NDVs, and null semantics to estimate
selectivity. On this non-null integer domain, including every possible month
should retain all rows; excluding every month should retain none.
Additional context
Isolates the membership predicates used in TPC-DS Q18/Q27 on a small numeric
domain. This is concrete evidence for the InList work already described in
#14237, suitable for a
focused sub-issue or a comment there.
Part of #25610.