[ https://issues.apache.org/jira/browse/CALCITE-5866?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17745321#comment-17745321 ]
Will Noble edited comment on CALCITE-5866 at 7/21/23 12:40 AM: --------------------------------------------------------------- Actually, I think we could come up with a general solution using aliases. Working on top of the simple examples in my last comment, this could work: {code:sql} SELECT "JOB" FROM (SELECT "JOB", COUNT("ENAME") AS "$f1" FROM "scott"."EMP" GROUP BY "JOB") AS "t0" ORDER BY "JOB", "$f1" {code} (I have not actually tested this, but I believe it could work). The trick is that, for queries that do not support ordering in sub-queries, we must try to move the {{ORDER BY}} clause to the outer query when we insert a sub-query, make sure the sub-query has aliases, then sort by alias in the outer query. This may require complex logic to make sure it doesn't interfere with other sorts, but I can't yet see a reason why it wouldn't work. Is there anything in the rel-to-sql converter that already works kinda like this? was (Author: wnoble): Actually, I think we could come up with a general solution using aliases. Working on top of the simple examples in my last comment, this could work: {code:sql} SELECT "JOB" FROM (SELECT "JOB", COUNT("ENAME") AS "$f1" FROM "scott"."EMP" GROUP BY "JOB") AS "t0" ORDER BY "JOB", "$f1" {code} (I have not actually tested this, but I believe it could work). The trick is that, for queries that do not support ordering in sub-queries, we must try to move the {{ORDER BY}} clause to the outer query when we insert a sub-query, make sure the sub-query has aliases, then sort by alias in the outer query. > Not all dialects support sorting in sub-queries > ----------------------------------------------- > > Key: CALCITE-5866 > URL: https://issues.apache.org/jira/browse/CALCITE-5866 > Project: Calcite > Issue Type: Bug > Reporter: Will Noble > Priority: Minor > > The rel-to-sql converter inserts subqueries in certain situations, such as > when sorting by ordinal (see CALCITE-5768). Certain dialects, such as MSSQL > (SQL Server) ignore the {{ORDER BY}} clause in a subquery. We may need a new > dialect-level setting like {{canSortInSubQuery}} that dictates whether > Calcite can safely insert sub-queries with {{ORDER BY}} clauses in them, or > whether it must do everything in it's power to avoid that (such as refusing > to sort by ordinal in cases where not all sort expressions are included in > the {{SELECT}} list). -- This message was sent by Atlassian Jira (v8.20.10#820010)