Repository navigation
Commit e7d9971
authored
fix: do not extract common subexpressions from the arguments of an aggregate with a FILTER (apache#26184)
## Which issue does this PR close?
- Closes apache#26183.
## Rationale for this change
An aggregate with a `FILTER` clause evaluates its arguments only for the
rows that pass the filter, so a fallible argument does not fail on a
rejected row.
When two aggregates have the same argument, `CommonSubexprEliminate`
moves that argument into a projection below the aggregate.
The projection evaluates it for every row, and the query fails on a row
that the filter rejects:
```sql
CREATE TABLE t AS
SELECT * FROM (VALUES (CAST(-950 AS SMALLINT), 0), (CAST(5 AS SMALLINT), 1)) AS v(x, y);
-- Succeeds.
SELECT avg(arrow_cast(x, 'UInt64')) FILTER (WHERE y > 0) AS a FROM t;
-- Fails: Arrow error: Cast error: Can't cast value -950 to type UInt64
SELECT
sum(arrow_cast(x, 'UInt64')) FILTER (WHERE y > 0) AS s,
count(arrow_cast(x, 'UInt64')) FILTER (WHERE y > 0) AS c
FROM t;
```
The rule already has a model for this: a conditionally evaluated
subexpression (a `CASE` branch, the right side of `AND` / `OR`, a lazy
function argument) is extracted only if it is also surely evaluated
somewhere else.
The arguments of a filtered aggregate are conditionally evaluated in the
same sense, but the rule did not know that.
## What changes are included in this PR?
1. `ExprCSEController::conditional_children` now reports the arguments
and the `ORDER BY` expressions of an `Expr::AggregateFunction` with a
`FILTER` as conditionally evaluated.
The filter itself stays a normal child, because the aggregate evaluates
it for every row.
A filter that several aggregates share is still extracted.
2. The same function reports the arguments of an `Expr::WindowFunction`
with a `FILTER` as conditionally evaluated.
The `PARTITION BY` expressions, the `ORDER BY` expressions and the
filter stay normal children.
The window executor does not yet evaluate the filter first (apache#24507,
apache#24508), so this part has no visible effect today.
It keeps the rule correct when that fix merges.
3. `CSEVisitor::f_down` in `datafusion/common/src/cse.rs` now visits the
children of a node with conditional children in tree order.
Before, it visited all normal children and then all conditional
children.
`CSERewriter` reads `id_array` in pre-order, so that was correct only
when every normal child came before every conditional child in the tree.
The children of an aggregate are `args`, `filter`, `order_by`, so the
normal child is in the middle.
The `CSEController` trait is unchanged.
The rule keeps every extraction that is safe:
| Query shape | Before | After |
|---|---|---|
| `sum(f(x)) FILTER (WHERE p), count(f(x)) FILTER (WHERE p)` | `f(x)`
and `p` extracted | only `p` extracted |
| `sum(f(x)) FILTER (WHERE p), count(f(x))` | `f(x)` extracted | `f(x)`
extracted (the second aggregate evaluates it for every row) |
| `sum(f(x)) FILTER (WHERE f(x) > 0)` | `f(x)` extracted | `f(x)`
extracted (the filter evaluates it for every row) |
| `sum(f(x)) FILTER (WHERE p) AS a, sum(f(x)) FILTER (WHERE p) AS b` |
one aggregate | one aggregate |
The cost is the first row: when an expression occurs only under filtered
aggregates, each aggregate now evaluates it, on the rows that pass its
filter.
An alternative keeps that extraction when every aggregate has the same
filter, with a guard such as `CASE WHEN p THEN f(x) END` in the
projection.
I did not do that here because it is a larger change with its own plan
shape, and it can be a follow-up.
## What is the testing strategy for this PR?
- Unit tests in `common_subexpr_eliminate.rs`:
`test_aggregate_filter_arguments` (the four rows of the table above),
`test_aggregate_filter_order_by` and `test_window_filter_arguments`.
- `cse.slt`: the reproduction above, with and without `GROUP BY`, the
`EXPLAIN` that shows the cast stays in the aggregate, and the case where
an unfiltered aggregate shares the argument.
- `aggregates_simplify.slt`: one expected plan changes. `SUM(column1 +
1) FILTER (WHERE column1 > 1), SUM(column1 + 1) FILTER (WHERE column1 >
0)` no longer extracts `column1 + 1`.
## Are there any user-facing changes?
Queries that failed with an error from a row that an aggregate `FILTER`
rejects now succeed.
Plans for queries where a subexpression occurs only in the arguments of
filtered aggregates no longer have that subexpression in a
`__common_expr` projection.
There are no API changes.1 parent 4ab4721 commit e7d9971
4 files changed
Lines changed: 318 additions & 19 deletions
File tree
- datafusion
- common/src
- optimizer/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
26 | 26 | | |
27 | 27 | | |
28 | 28 | | |
29 | | - | |
| 29 | + | |
30 | 30 | | |
31 | 31 | | |
32 | 32 | | |
| |||
176 | 176 | | |
177 | 177 | | |
178 | 178 | | |
| 179 | + | |
| 180 | + | |
| 181 | + | |
179 | 182 | | |
180 | 183 | | |
181 | 184 | | |
| |||
363 | 366 | | |
364 | 367 | | |
365 | 368 | | |
366 | | - | |
367 | | - | |
368 | | - | |
369 | | - | |
370 | | - | |
371 | | - | |
| 369 | + | |
| 370 | + | |
| 371 | + | |
| 372 | + | |
| 373 | + | |
| 374 | + | |
372 | 375 | | |
373 | | - | |
| 376 | + | |
| 377 | + | |
| 378 | + | |
| 379 | + | |
| 380 | + | |
| 381 | + | |
| 382 | + | |
| 383 | + | |
374 | 384 | | |
375 | 385 | | |
376 | 386 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
30 | 30 | | |
31 | 31 | | |
32 | 32 | | |
33 | | - | |
| 33 | + | |
| 34 | + | |
| 35 | + | |
| 36 | + | |
34 | 37 | | |
35 | 38 | | |
36 | 39 | | |
| |||
691 | 694 | | |
692 | 695 | | |
693 | 696 | | |
| 697 | + | |
| 698 | + | |
| 699 | + | |
| 700 | + | |
| 701 | + | |
| 702 | + | |
| 703 | + | |
| 704 | + | |
| 705 | + | |
| 706 | + | |
| 707 | + | |
| 708 | + | |
| 709 | + | |
| 710 | + | |
| 711 | + | |
| 712 | + | |
| 713 | + | |
| 714 | + | |
| 715 | + | |
| 716 | + | |
| 717 | + | |
| 718 | + | |
| 719 | + | |
| 720 | + | |
| 721 | + | |
| 722 | + | |
| 723 | + | |
| 724 | + | |
| 725 | + | |
| 726 | + | |
| 727 | + | |
| 728 | + | |
| 729 | + | |
| 730 | + | |
| 731 | + | |
| 732 | + | |
| 733 | + | |
| 734 | + | |
| 735 | + | |
| 736 | + | |
| 737 | + | |
| 738 | + | |
| 739 | + | |
| 740 | + | |
| 741 | + | |
| 742 | + | |
| 743 | + | |
| 744 | + | |
| 745 | + | |
| 746 | + | |
| 747 | + | |
| 748 | + | |
| 749 | + | |
| 750 | + | |
| 751 | + | |
| 752 | + | |
694 | 753 | | |
695 | 754 | | |
696 | 755 | | |
| |||
842 | 901 | | |
843 | 902 | | |
844 | 903 | | |
845 | | - | |
846 | | - | |
847 | | - | |
| 904 | + | |
| 905 | + | |
| 906 | + | |
848 | 907 | | |
849 | 908 | | |
850 | 909 | | |
| |||
853 | 912 | | |
854 | 913 | | |
855 | 914 | | |
856 | | - | |
| 915 | + | |
857 | 916 | | |
858 | 917 | | |
859 | 918 | | |
| |||
946 | 1005 | | |
947 | 1006 | | |
948 | 1007 | | |
949 | | - | |
| 1008 | + | |
950 | 1009 | | |
951 | 1010 | | |
952 | 1011 | | |
| |||
1948 | 2007 | | |
1949 | 2008 | | |
1950 | 2009 | | |
| 2010 | + | |
| 2011 | + | |
| 2012 | + | |
| 2013 | + | |
| 2014 | + | |
| 2015 | + | |
| 2016 | + | |
| 2017 | + | |
| 2018 | + | |
| 2019 | + | |
| 2020 | + | |
| 2021 | + | |
| 2022 | + | |
| 2023 | + | |
| 2024 | + | |
| 2025 | + | |
| 2026 | + | |
| 2027 | + | |
| 2028 | + | |
| 2029 | + | |
| 2030 | + | |
| 2031 | + | |
| 2032 | + | |
| 2033 | + | |
| 2034 | + | |
| 2035 | + | |
| 2036 | + | |
| 2037 | + | |
| 2038 | + | |
| 2039 | + | |
| 2040 | + | |
| 2041 | + | |
| 2042 | + | |
| 2043 | + | |
| 2044 | + | |
| 2045 | + | |
| 2046 | + | |
| 2047 | + | |
| 2048 | + | |
| 2049 | + | |
| 2050 | + | |
| 2051 | + | |
| 2052 | + | |
| 2053 | + | |
| 2054 | + | |
| 2055 | + | |
| 2056 | + | |
| 2057 | + | |
| 2058 | + | |
| 2059 | + | |
| 2060 | + | |
| 2061 | + | |
| 2062 | + | |
| 2063 | + | |
| 2064 | + | |
| 2065 | + | |
| 2066 | + | |
| 2067 | + | |
| 2068 | + | |
| 2069 | + | |
| 2070 | + | |
| 2071 | + | |
| 2072 | + | |
| 2073 | + | |
| 2074 | + | |
| 2075 | + | |
| 2076 | + | |
| 2077 | + | |
| 2078 | + | |
| 2079 | + | |
| 2080 | + | |
| 2081 | + | |
| 2082 | + | |
| 2083 | + | |
| 2084 | + | |
| 2085 | + | |
| 2086 | + | |
| 2087 | + | |
| 2088 | + | |
| 2089 | + | |
| 2090 | + | |
| 2091 | + | |
| 2092 | + | |
| 2093 | + | |
| 2094 | + | |
| 2095 | + | |
| 2096 | + | |
| 2097 | + | |
| 2098 | + | |
| 2099 | + | |
| 2100 | + | |
| 2101 | + | |
| 2102 | + | |
| 2103 | + | |
| 2104 | + | |
| 2105 | + | |
| 2106 | + | |
| 2107 | + | |
| 2108 | + | |
| 2109 | + | |
| 2110 | + | |
| 2111 | + | |
| 2112 | + | |
| 2113 | + | |
| 2114 | + | |
| 2115 | + | |
| 2116 | + | |
| 2117 | + | |
| 2118 | + | |
| 2119 | + | |
| 2120 | + | |
| 2121 | + | |
| 2122 | + | |
| 2123 | + | |
| 2124 | + | |
| 2125 | + | |
| 2126 | + | |
| 2127 | + | |
| 2128 | + | |
| 2129 | + | |
| 2130 | + | |
| 2131 | + | |
| 2132 | + | |
| 2133 | + | |
| 2134 | + | |
| 2135 | + | |
| 2136 | + | |
| 2137 | + | |
| 2138 | + | |
| 2139 | + | |
| 2140 | + | |
| 2141 | + | |
| 2142 | + | |
| 2143 | + | |
| 2144 | + | |
| 2145 | + | |
| 2146 | + | |
| 2147 | + | |
| 2148 | + | |
| 2149 | + | |
| 2150 | + | |
| 2151 | + | |
| 2152 | + | |
| 2153 | + | |
| 2154 | + | |
| 2155 | + | |
| 2156 | + | |
| 2157 | + | |
| 2158 | + | |
| 2159 | + | |
| 2160 | + | |
| 2161 | + | |
| 2162 | + | |
| 2163 | + | |
| 2164 | + | |
| 2165 | + | |
| 2166 | + | |
| 2167 | + | |
| 2168 | + | |
| 2169 | + | |
1951 | 2170 | | |
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
226 | 226 | | |
227 | 227 | | |
228 | 228 | | |
229 | | - | |
230 | | - | |
231 | | - | |
| 229 | + | |
| 230 | + | |
232 | 231 | | |
233 | 232 | | |
234 | | - | |
235 | | - | |
| 233 | + | |
236 | 234 | | |
237 | 235 | | |
238 | 236 | | |
| |||
0 commit comments