You are viewing a plain text version of this content. The canonical link for it is here.

Posted to github@arrow.apache.org by GitBox <gi...@apache.org> on 2023/01/08 16:12:05 UTC

[GitHub] [arrow-datafusion] alamb opened a new issue, #4854: can not ORDER BY a an aliased group column

alamb opened a new issue, #4854:
URL: https://github.com/apache/arrow-datafusion/issues/4854

   **Describe the bug**
   A clear and concise description of what the bug is.
   
   **To Reproduce**
   using datafusion-cli load some data:
   
   ```sql
   ❯ create or replace table t as select column1 as value, column2 as time from (select * from (values
     (1, timestamp '2022-01-01 00:00:00'),
     (2, timestamp '2022-01-01 01:00:00'),
     (3, timestamp '2022-01-02 00:00:00')
   ) as sq) as sq
   ;
   
   0 rows in set. Query took 0.001 seconds.
   ❯ select *  from t;
   +-------+---------------------+
   | value | time                |
   +-------+---------------------+
   | 1     | 2022-01-01T00:00:00 |
   | 2     | 2022-01-01T01:00:00 |
   | 3     | 2022-01-02T00:00:00 |
   +-------+---------------------+
   3 rows in set. Query took 0.001 seconds.
   ❯ ;
   ```
   
   `GROUP BY "time"` works:
   
   ```sql
   ❯ select
     sum(value) AS "value",
     date_trunc('hour',time) AS "time"
   FROM t
   GROUP BY time;
   +-------+---------------------+
   | value | time                |
   +-------+---------------------+
   | 3     | 2022-01-02T00:00:00 |
   | 2     | 2022-01-01T01:00:00 |
   | 1     | 2022-01-01T00:00:00 |
   +-------+---------------------+
   3 rows in set. Query took 0.002 seconds.
   ```
   
   However, `GROUP BY "time" ORDER BY "time"` does not work:
   
   ```sql
   ❯ select
     sum(value) AS "value",
     date_trunc('month',time) AS "time"
   FROM t
   GROUP BY time
   ORDER BY time;
   
   SchemaError(AmbiguousReference { qualifier: Some("t"), name: "time" })
   ```
   
   **Expected behavior**
   I expect the query to run and produce ordered results like postgres:
   
   ```sql
   postgres=# select
     sum(value) AS "value",
     date_trunc('month',time) AS "time"
   FROM t
   GROUP BY time
   ORDER BY time;
    value |        time         
   -------+---------------------
        3 | 2022-01-01 00:00:00
        2 | 2022-01-01 00:00:00
        1 | 2022-01-01 00:00:00
   (3 rows)
   ```
   
   **Additional context**
   
   Note I was confused about why this query wasn't actually aggregating on the truncated date (as in why are there 3 output rows rather than 1 output row). The reason is that the `GROUP BY time` is (correctly) evaluated before the `date_trunc` function evaluated in the select list.
   
   To group by the truncated date, you  need to group by `date_trunc('month',time) AS "time"` , which you can do using `2` 
   
   ```sql
   select
     sum(value) AS "value",
     date_trunc('month',time) AS "time"
   FROM t
   GROUP BY 2
   ORDER BY 2;
   +-------+---------------------+
   | value | time                |
   +-------+---------------------+
   | 6     | 2022-01-01T00:00:00 |
   +-------+---------------------+
   1 row in set. Query took 0.005 seconds.
   ```
   
   
   


-- 
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-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org