kumarUjjawal opened a new pull request, #27548: URL: https://github.com/apache/datafusion/pull/27548
## Which issue does this PR close? - Closes #25621. - Part of #25610. ## Rationale for this change `FilterExec` estimated every `col IN (...)` and `col NOT IN (...)` at the 20% default. On TPC-DS SF1 `date_dim`, `d_moy IN (1, ..., 12)` gave 14,610 rows instead of 73,049, and `d_moy NOT IN (1, ..., 12)` gave 14,610 instead of 0. ## What changes are included in this PR? - When the whole predicate is one `col [NOT] IN (literals)`, the estimate is the share of the column's distinct values that the list names, within the non-null rows. Listed values outside min and max match nothing. `NOT IN` with a NULL item matches nothing. - The distinct count comes from the column statistics. Without one, integer and `Date32` columns use the number of values from min to max, capped at the non-null rows. Other columns keep the default, because their ranges hold many more values than the column. - An `IN` list inside an `AND`, or with a non-literal item, keeps the default. #25612 covers `AND` predicates. - `IN` and `NOT IN` now reject NULLs in the output column statistics, as `LIKE` does. - `unique_match_limit` and the new estimate share one list parser. The null-row scaling moved unchanged from `string_selectivity` into a shared helper. `dfbench statistics` on TPC-DS SF1 (data from apache/datafusion-benchmarks@32f67477): | Query | Predicate | Before | After | Actual | | --- | --- | ---: | ---: | ---: | | q23 | `d_year IN (4 years)` | 14,610 | 1,454 | 1,461 | | q29 | `d_year IN (3 years)` | 14,610 | 1,091 | 1,096 | | q53, q63 | `d_month_seq IN (12 months)` | 14,610 | 366 | 365 | | q45 | `i_item_sk IN (10 keys)` | 3,600 | 10 | 10 | - All 14 changed `FilterExec` estimates (10 `IN` filters and 4 filters above them in q23) moved closer to the actual rows. The p50 q-error went from 3.00x to 2.97x. - Only q45 changed plan shape: the hash join now builds from the 10-row `item` filter. Over 10 iterations, q45 took 392.8 ms on `main` and 385.7 ms with this PR. q53 and the untouched q3 stayed within 1%. - In q23 the plan did not change. But the joins and aggregates above the more accurate date filter now estimate fewer rows, so their q-error grew (for example, 143x to 1,441x). The 10x over-estimate of the filter hid an existing join under-estimate. ## What is the testing strategy for this PR? - `parquet_statistics.slt` checks `EXPLAIN` row estimates for `IN` and `NOT IN` on a Parquet file without a distinct count. - `sql_in_list_filter_selectivity` in `custom_sources_cases/statistics.rs` checks SQL predicates on integer and string columns. - Unit tests in `filter.rs` cover the issue's cases, out-of-range and repeated values, NULL items and bounds, null and row counts, the types that keep the default, and predicates that keep the default. - `test_filter_statistics_in_list_on_a_unique_column` now expects `NOT IN` of 3 of 100 unique values to give 97 rows, not the default 20. - `./dev/rust_lint.sh` passes. ## Are there any user-facing changes? Row estimates change for filters that are one `IN` or `NOT IN` list of literals, and this can change plans that use them. There is no API change. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
