Skip to content

Estimate IN and NOT IN selectivity from the listed values #25621

Description

@gabotechs

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.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions