[jira] [Commented] (ARROW-9520) [Rust] [DataFusion] Can't alias an aggregate expression

["Andrew Lamb (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/ARROW-9520?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17188500#comment-17188500 ] 

Andrew Lamb commented on ARROW-9520:
------------------------------------

Here is another example:

{code}
> create external table sales(customer_id varchar, sales bigint) stored as CSV location '/tmp/foo.csv';
0 rows in set. Query took 0 seconds.

> SELECT customer_id, sum(sales) FROM sales ORDER BY sum(sales);
General("Projection references non-aggregate values")
{code}

> [Rust] [DataFusion] Can't alias an aggregate expression
> -------------------------------------------------------
>
>                 Key: ARROW-9520
>                 URL: https://issues.apache.org/jira/browse/ARROW-9520
>             Project: Apache Arrow
>          Issue Type: Bug
>          Components: Rust - DataFusion
>            Reporter: Jorge
>            Priority: Major
>
> The following test (on execute) fails:
> {code}
>     #[test]
>     fn aggregate_with_alias() -> Result<()> {
>         let results = execute("SELECT c1, COUNT(c2) AS count FROM test GROUP BY c1", 4)?;
>         assert_eq!(field_names(batch), vec!["c1", "count"]);
>         let expected = vec!["0,10", "1,10", "2,10", "3,10"];
>         let mut rows = test::format_batch(&batch);
>         rows.sort();
>         assert_eq!(rows, expected);
>         Ok(())
>     }
> {code}
> The root cause is that, in {{sql::planner}}, we interpret {{COUNT(c2) AS count}} as An {{Expr::Alias}}, which fails the {{is_aggregate_expr}} condition, thus being interpreted as grouped expression instead of an aggregated expression. This raises the Error
> {{General("Projection references non-aggregate values")}}
> The planner could interpret the statement above as two steps: an aggregation followed by a projection. Alternatively, we can allow aliases to be valid aggregation expressions.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)