devinjdangelo commented on code in PR #10767:
URL: https://github.com/apache/datafusion/pull/10767#discussion_r1624265772
##########
datafusion/sql/tests/cases/plan_to_sql.rs:
##########
@@ -127,7 +127,10 @@ fn roundtrip_statement() -> Result<()> {
UNION ALL
SELECT j2_string as string FROM j2
ORDER BY string DESC
- LIMIT 10"#
+ LIMIT 10"#,
+ "SELECT id, count(*) over (PARTITION BY first_name ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
Review Comment:
It appears that the datafusion planner does not support mixing aggregate and
window functions. It does allow mixing window functions with different
WindowSpecs, including some over all rows (which is effectively the same thing
as an aggregate function).
```bash
DataFusion CLI v38.0.0
> create table person (id int, name varchar);
0 row(s) fetched.
Elapsed 0.001 seconds.
> insert into person values (1, 'a'), (2, 'b'), (3, 'c');
+-------+
| count |
+-------+
| 3 |
+-------+
1 row(s) fetched.
Elapsed 0.001 seconds.
> select count(distinct id), sum(id) over (partition by name) from person;
Error during planning: Projection references non-aggregate values:
Expression person.id could not be resolved from available columns:
COUNT(DISTINCT person.id)
> select count(distinct id) over (), sum(id) over (partition by name) from
person;
+---------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
| COUNT(person.id) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
| SUM(person.id) PARTITION BY [person.name] ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING |
+---------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
| 3
| 2
|
| 3
| 1
|
| 3
| 3
|
+---------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------+
3 row(s) fetched.
Elapsed 0.002 seconds.
```
I added some tests and made a few changes to correctly support unparsing a
SELECT query with multiple different WindowSpecs.
--
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: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
