[GitHub] [druid] john-bodley opened a new issue #9186: Druid SQL ORDER BY clause parse/documentation issue

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

john-bodley opened a new issue #9186: Druid SQL ORDER BY clause parse/documentation issue 
URL: https://github.com/apache/druid/issues/9186
 
 
   ### Affected Version
   
   0.13.0.3.
   
   ### Description
   
   Some SQL databases, i.e., MySQL, support expression aliases in the `ORDER BY` clause, whereas others, i.e., SQLite, do not (I gather this is due to the execution order). According to the [Druid SQL](https://druid.apache.org/docs/latest/querying/sql.html#query-syntax) documentation for the `ORDER BY` clause it mentions, 
   
   > ORDER BY can refer to an expression or a select clause ordinal position (like ORDER BY 2 to order by the second selected column)
   
   and thus it would suggest that expression aliases are not support. 
   
   The following query which uses an expression in the `SELECT` clause, 
   
   ```
   SELECT 
       FLOOR("__time" TO WEEK),
       SUM(foo) / SUM(bar) AS "foo_by_bar",
       SUM(foo) AS "foo"
   FROM 
      my_table
   GROUP BY 
       FLOOR("__time" TO WEEK)
   ORDER BY 
       SUM(foo) / SUM(bar) DESC
   ```
   
   fails with the following error, 
   
   ```
   druid error: Unknown exception (org.apache.druid.java.util.common.ISE): Cannot build plan for query: SELECT ...
   ```
   
   which seems to be related to the fact that the "foo" column alias masks the `foo` column, i.e., the following works (renaming the "foo" alias to "my_foo"), 
   
   ```
   SELECT 
       FLOOR("__time" TO WEEK),
       SUM(foo) / SUM(bar) AS "foo_by_bar",
       SUM(foo) AS "my_foo"
   FROM 
      my_table
   GROUP BY 
       FLOOR("__time" TO WEEK)
   ORDER BY 
       SUM(foo) / SUM(bar) DESC
   ```
   
   I'm a little perplexed as this would suggest that the `SELECT` clause is potentially being processed/parsed prior to the `ORDER BY` clause. Although not documented it seems that `ORDER BY` can also refer to an expression alias, i.e., the following works:
   
   ```
   SELECT 
       FLOOR("__time" TO WEEK),
       SUM(foo) / SUM(bar) AS "foo_by_bar",
       SUM(foo) AS "foo"
   FROM 
      my_table
   GROUP BY 
       FLOOR("__time" TO WEEK)
   ORDER BY 
       "foo_by_bar" DESC
   ```
   
   It seems that from the first example there may be a parsing issue and from the third example it seems like the documentation doesn't accurately reflect what the `ORDER BY` clause can actually refer to.

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org