Repository navigation
Commit 7e5f40a
fix: reject window functions in WHERE and HAVING during logical planning (#25139)
## Which issue does this PR close?
- Closes #4610
## Rationale for this change
Run these statements in `datafusion-cli`:
```sql
CREATE TABLE t(a INT, b INT) AS VALUES (1, 1), (2, 2);
SELECT a FROM t GROUP BY a HAVING row_number() OVER () = 1;
```
On `main`, the second statement fails during physical planning. The
error is the `Debug` text of the internal `Expr::WindowFunction`. It is
one line of 526 characters. It does not give the clause, the function,
or the column:
```
This feature is not implemented: Physical plan does not support logical expression WindowFunction(WindowFunction { fun: WindowUDF(WindowUDF { inner: RowNumber { signature: Signature { type_signature: Nullary, volatility: Immutable, parameter_names: None } } }), params: WindowFunctionParams { args: [], partition_by: [], order_by: [], window_frame: WindowFrame { units: Rows, start_bound: Preceding(UInt64(NULL)), end_bound: Following(UInt64(NULL)), is_causal: false }, filter: None, null_treatment: None, distinct: false } })
```
The query from the issue gives the same type of error. That error is
1,981 characters long.
With this PR, the statement fails during logical planning. The error
gives the clause and the window function:
```
Error during planning: Window function calls are not allowed in HAVING: 'row_number() ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'
```
The same applies to `WHERE`:
```sql
SELECT a FROM t WHERE row_number() OVER () = 1;
```
```
Error during planning: Window function calls are not allowed in WHERE: 'row_number() ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'
```
The error has a `Diagnostic`. Its span points to the window function
call in the SQL text, or to the alias that resolves to one. Its help
message gives the fix:
```
Move the condition that uses this window function to a QUALIFY clause, which is evaluated after window functions are computed
```
These queries are not valid SQL. DataFusion evaluates a filter before it
computes window functions. PostgreSQL rejects the same queries with
`window functions are not allowed in HAVING`.
These queries continue to work:
```sql
-- window function in the SELECT list
SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total FROM t GROUP BY a;
-- window function in QUALIFY
SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total FROM t GROUP BY a QUALIFY sum(count(*)) OVER () >= 2;
-- window function in an inner query
SELECT * FROM (SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total FROM t GROUP BY a) s WHERE total >= 2;
-- window function in a subquery of a WHERE predicate
SELECT a FROM t WHERE a IN (SELECT a FROM t QUALIFY row_number() OVER (ORDER BY a) = 1);
```
## What changes are included in this PR?
Five stacked commits:
1. `fix: reject window functions in WHERE and HAVING during logical
planning`
- Add `datafusion_expr::utils::check_no_window_functions(expr, clause)`.
It returns a planning error if `expr` contains a window function. The
function does not look into subqueries. It has the same shape as
`check_aggregate_and_window_nesting` from #23813.
- `Filter::try_new` calls the function with the clause name `filter
predicates`. This applies to the `DataFrame` and `LogicalPlanBuilder`
paths. `Filter::new` does not do the check. Its documentation says this.
- The SQL planner calls the function for `WHERE` and for `HAVING`. The
`HAVING` check runs after alias resolution. Thus `HAVING total >= 10`,
where `total` is an alias of a window function, is also rejected.
2. `refactor: expose window_function_not_allowed_err from
datafusion_expr::utils`
- Split the error construction out of `check_no_window_functions` so a
caller with better location information can build the same error.
3. `feat: point the window-in-WHERE/HAVING diagnostic at the call,
suggest QUALIFY`
- The SQL planner takes the span from the sqlparser AST. A planned
`Expr` only has spans on columns, so before this the span pointed to the
first column inside the call, and `row_number() OVER ()` had no span.
Window calls in subqueries are skipped.
- The help message tells the user to move the condition to `QUALIFY`.
This is the one rewrite that always gives the query its standard
meaning.
4. `fix: readable physical planning errors for misplaced window and
aggregate functions`
- The physical planner is the backstop for every other position, and for
plans built with the DataFrame API. Its error for a window or aggregate
function now names the function and lists the positions where it is
supported, instead of the `Debug` dump. The `LIMIT` and `OFFSET` errors
print the expression with `Display`.
5. `fix: reject window functions in JOIN ON, GROUP BY and VALUES during
SQL planning`
- The same check as for `WHERE` and `HAVING`, with a clause-specific
hint. `GROUP BY` is checked after aliases and positions are resolved.
The SQL planner checks are for better errors on common mistakes: they
carry a span into the SQL text and a hint for the clause. They are not
needed for correctness. The physical planner rejects a misplaced window
or aggregate function in every position, but it cannot point into the
query.
## What is the testing strategy for this PR?
- `datafusion/expr/src/utils.rs`: unit test for
`check_no_window_functions`. It checks the accepted expressions, the
error message, and the diagnostic.
- `datafusion/expr/src/logical_plan/builder.rs`:
`LogicalPlanBuilder::filter` and `having` reject a window predicate.
`window(...).filter(...)` continues to work.
- `datafusion/sql/tests/sql_integration.rs`: the `WHERE` and `HAVING`
messages, the alias case, and the `QUALIFY` and subquery forms.
- `datafusion/sql/tests/cases/diagnostic.rs`: the diagnostic span points
to the window function call in the SQL text, to the alias in `HAVING`,
and not to a call inside a subquery. The help message is checked.
- `datafusion/sqllogictest/test_files/window.slt`: the reproducer from
the issue, the `WHERE` variant, the four queries that continue to work,
the `JOIN ON`, `GROUP BY` and `VALUES` errors, and the physical planner
backstop for `LIMIT` and `OFFSET`.
- `datafusion/core/tests/dataframe/mod.rs`: a window function as a
DataFrame grouping expression reaches the physical planner backstop.
## Are there any user-facing changes?
Yes. A query with a window function in `WHERE` or `HAVING` now fails
during logical planning. The error is `Error during planning: Window
function calls are not allowed in {WHERE|HAVING}: '<window call>'`.
Before, the query failed during physical planning with `This feature is
not implemented: Physical plan does not support logical expression
WindowFunction(...)`.
`Filter::try_new`, `LogicalPlanBuilder::filter` and
`LogicalPlanBuilder::having` return the same type of error for such a
predicate. Before, they returned a plan that could not be executed.
A window or aggregate function in any other position now fails with
`Error during planning: Window function '<call>' is not supported in
this position. Window functions are supported in the SELECT list, ORDER
BY, DISTINCT ON and QUALIFY` (or the aggregate equivalent) instead of
`This feature is not implemented: Physical plan does not support logical
expression WindowFunction(...)`. The `Unsupported LIMIT expression` and
`Unsupported OFFSET expression` errors show the expression as SQL text.
New public API: `datafusion_expr::utils::check_no_window_functions` and
`datafusion_expr::utils::window_function_not_allowed_err`.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
---------
Co-authored-by: Claude Fable 5.1 <[email protected]>1 parent 4cee381 commit 7e5f40a
13 files changed
Lines changed: 704 additions & 20 deletions
File tree
- datafusion
- core
- src
- tests/dataframe
- expr/src
- logical_plan
- physical-expr/src
- sqllogictest/test_files
- sql
- src
- expr
- relation
- tests
- cases
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
1270 | 1270 | | |
1271 | 1271 | | |
1272 | 1272 | | |
| 1273 | + | |
| 1274 | + | |
1273 | 1275 | | |
| 1276 | + | |
1274 | 1277 | | |
1275 | | - | |
1276 | | - | |
| 1278 | + | |
| 1279 | + | |
1277 | 1280 | | |
1278 | 1281 | | |
1279 | 1282 | | |
| 1283 | + | |
1280 | 1284 | | |
1281 | | - | |
1282 | | - | |
| 1285 | + | |
| 1286 | + | |
1283 | 1287 | | |
1284 | 1288 | | |
1285 | 1289 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
5334 | 5334 | | |
5335 | 5335 | | |
5336 | 5336 | | |
| 5337 | + | |
| 5338 | + | |
| 5339 | + | |
| 5340 | + | |
| 5341 | + | |
| 5342 | + | |
| 5343 | + | |
| 5344 | + | |
| 5345 | + | |
| 5346 | + | |
| 5347 | + | |
| 5348 | + | |
| 5349 | + | |
| 5350 | + | |
| 5351 | + | |
| 5352 | + | |
| 5353 | + | |
| 5354 | + | |
| 5355 | + | |
5337 | 5356 | | |
5338 | 5357 | | |
5339 | 5358 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
3131 | 3131 | | |
3132 | 3132 | | |
3133 | 3133 | | |
| 3134 | + | |
| 3135 | + | |
| 3136 | + | |
| 3137 | + | |
| 3138 | + | |
| 3139 | + | |
| 3140 | + | |
| 3141 | + | |
| 3142 | + | |
| 3143 | + | |
| 3144 | + | |
| 3145 | + | |
| 3146 | + | |
| 3147 | + | |
| 3148 | + | |
| 3149 | + | |
| 3150 | + | |
| 3151 | + | |
| 3152 | + | |
| 3153 | + | |
| 3154 | + | |
| 3155 | + | |
| 3156 | + | |
| 3157 | + | |
| 3158 | + | |
| 3159 | + | |
| 3160 | + | |
| 3161 | + | |
| 3162 | + | |
| 3163 | + | |
| 3164 | + | |
| 3165 | + | |
| 3166 | + | |
| 3167 | + | |
| 3168 | + | |
| 3169 | + | |
| 3170 | + | |
| 3171 | + | |
| 3172 | + | |
| 3173 | + | |
| 3174 | + | |
| 3175 | + | |
| 3176 | + | |
| 3177 | + | |
3134 | 3178 | | |
3135 | 3179 | | |
3136 | 3180 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
41 | 41 | | |
42 | 42 | | |
43 | 43 | | |
44 | | - | |
45 | | - | |
46 | | - | |
| 44 | + | |
| 45 | + | |
| 46 | + | |
| 47 | + | |
47 | 48 | | |
48 | 49 | | |
49 | 50 | | |
| |||
2822 | 2823 | | |
2823 | 2824 | | |
2824 | 2825 | | |
2825 | | - | |
2826 | | - | |
| 2826 | + | |
| 2827 | + | |
2827 | 2828 | | |
2828 | 2829 | | |
2829 | 2830 | | |
2830 | 2831 | | |
| 2832 | + | |
2831 | 2833 | | |
2832 | 2834 | | |
2833 | 2835 | | |
2834 | 2836 | | |
2835 | 2837 | | |
2836 | 2838 | | |
2837 | 2839 | | |
| 2840 | + | |
| 2841 | + | |
| 2842 | + | |
| 2843 | + | |
2838 | 2844 | | |
2839 | 2845 | | |
2840 | 2846 | | |
| |||
2853 | 2859 | | |
2854 | 2860 | | |
2855 | 2861 | | |
| 2862 | + | |
| 2863 | + | |
| 2864 | + | |
| 2865 | + | |
| 2866 | + | |
2856 | 2867 | | |
2857 | 2868 | | |
2858 | 2869 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
750 | 750 | | |
751 | 751 | | |
752 | 752 | | |
| 753 | + | |
| 754 | + | |
| 755 | + | |
| 756 | + | |
| 757 | + | |
| 758 | + | |
| 759 | + | |
| 760 | + | |
| 761 | + | |
| 762 | + | |
| 763 | + | |
| 764 | + | |
| 765 | + | |
| 766 | + | |
| 767 | + | |
| 768 | + | |
| 769 | + | |
| 770 | + | |
| 771 | + | |
| 772 | + | |
| 773 | + | |
| 774 | + | |
| 775 | + | |
| 776 | + | |
| 777 | + | |
| 778 | + | |
| 779 | + | |
| 780 | + | |
| 781 | + | |
| 782 | + | |
| 783 | + | |
| 784 | + | |
| 785 | + | |
| 786 | + | |
| 787 | + | |
| 788 | + | |
| 789 | + | |
| 790 | + | |
| 791 | + | |
| 792 | + | |
| 793 | + | |
| 794 | + | |
| 795 | + | |
| 796 | + | |
| 797 | + | |
| 798 | + | |
| 799 | + | |
| 800 | + | |
| 801 | + | |
| 802 | + | |
| 803 | + | |
| 804 | + | |
| 805 | + | |
| 806 | + | |
| 807 | + | |
| 808 | + | |
| 809 | + | |
| 810 | + | |
| 811 | + | |
| 812 | + | |
| 813 | + | |
| 814 | + | |
| 815 | + | |
| 816 | + | |
| 817 | + | |
| 818 | + | |
| 819 | + | |
| 820 | + | |
753 | 821 | | |
754 | 822 | | |
755 | 823 | | |
| |||
2102 | 2170 | | |
2103 | 2171 | | |
2104 | 2172 | | |
| 2173 | + | |
| 2174 | + | |
| 2175 | + | |
| 2176 | + | |
| 2177 | + | |
| 2178 | + | |
| 2179 | + | |
| 2180 | + | |
| 2181 | + | |
| 2182 | + | |
| 2183 | + | |
| 2184 | + | |
| 2185 | + | |
| 2186 | + | |
| 2187 | + | |
| 2188 | + | |
| 2189 | + | |
| 2190 | + | |
| 2191 | + | |
| 2192 | + | |
| 2193 | + | |
| 2194 | + | |
| 2195 | + | |
| 2196 | + | |
| 2197 | + | |
| 2198 | + | |
| 2199 | + | |
| 2200 | + | |
| 2201 | + | |
| 2202 | + | |
| 2203 | + | |
| 2204 | + | |
| 2205 | + | |
| 2206 | + | |
| 2207 | + | |
| 2208 | + | |
| 2209 | + | |
| 2210 | + | |
| 2211 | + | |
2105 | 2212 | | |
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
689 | 689 | | |
690 | 690 | | |
691 | 691 | | |
| 692 | + | |
| 693 | + | |
| 694 | + | |
| 695 | + | |
| 696 | + | |
| 697 | + | |
| 698 | + | |
| 699 | + | |
| 700 | + | |
| 701 | + | |
692 | 702 | | |
693 | 703 | | |
694 | 704 | | |
| |||
0 commit comments