[GitHub] [arrow-datafusion] crepererum commented on issue #4854: Can not ORDER BY an aliased group column

[GitBox <gi...@apache.org>]

crepererum commented on issue #4854:
URL: https://github.com/apache/arrow-datafusion/issues/4854#issuecomment-1377388995

   Note that `ORDER BY` uses the columns generated by `GROUP BY`, NOT the original columns. This can be checked w/ postgres:
   
   ```console
   postgres=# select
     -sum(value) AS "value",
     date_trunc('month',time) AS "time"
   FROM t
   GROUP BY time
   ORDER BY value;
    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)
   ```
   
   Also note that columns that are NOT part of the `GROUP BY` output cannot be used in `ORDER BY`:
   
   ```console
   postgres=# select
     date_trunc('month',time) AS "time"
   FROM t                              
   GROUP BY time
   ORDER BY value;
   ERROR:  column "t.value" must appear in the GROUP BY clause or be used in an aggregate function
   LINE 5: ORDER BY value;
   ```
   
   Looking at the current `LogicalPlanBuilder::sort` code it seems that the "missing column"-handling or the `rewrite_sort_cols_by_aggs` bit in there are somewhat broken.


-- 
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

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