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