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: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
