alamb opened a new issue, #9924: URL: https://github.com/apache/arrow-datafusion/issues/9924
### Describe the bug DataFusion now supports providing ordering and nulls information to aggregates For example ```sql ❯ select first_value(column1 ORDER BY column2) FROM (values (1,2), (3,4), (-1,0)) ; +----------------------+ | FIRST_VALUE(column1) | +----------------------+ | -1 | +----------------------+ ❯ select first_value(column1 ORDER BY column2) IGNORE NULLS FROM (values (1,2), (3,4), (null,0)) ; +----------------------+ | FIRST_VALUE(column1) | +----------------------+ | 1 | +----------------------+ 1 row in set. Query took 0.002 seconds. ``` ### To Reproduce ```sql ❯ select count(*) from (values (1), (null), (2)); +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set. Query took 0.039 seconds. ❯ select count(*) IGNORE NULLS from (values (1), (null), (2)); +----------+ | COUNT(*) | +----------+ | 3 | +----------+ 1 row in set. Query took 0.001 seconds. ``` Also, for ordering ```sql ❯ select avg(column1 ORDER BY column2) FROM (values (1,2), (3,4), (null,0)) ; +--------------+ | AVG(column1) | +--------------+ | 2.0 | +--------------+ 1 row in set. Query took 0.008 seconds. ``` ### Expected behavior I expect ```sql ❯ select count(*) IGNORE NULLS from (values (1), (null), (2)); ``` To error with "IGNORE NULLS is not supported I also expect ```sql ❯ select avg(column1 ORDER BY column2) FROM (values (1,2), (3,4), (null,0)) ; ``` to error with "ORDER BY" not supported for avg ### Additional context @jayzhan211 has some good ideas at https://github.com/apache/arrow-datafusion/pull/9920#discussion_r1549603208 about how to make checking this easier / harder to miss -- 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]
