[
https://issues.apache.org/jira/browse/CALCITE-5866?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17748764#comment-17748764
]
Will Noble commented on CALCITE-5866:
-------------------------------------
The main thing for us to decide is what the behavior should be for a Calcite
relational expression, then we can handle each dialect as necessary. If this
was already hashed out somewhere linkable I'd be curious to know what was said.
# We could decide that any sort or aggregate node effectively de-stabilizes any
prior sorts, meaning the ordering is lost but the limit and offset are kept. I
believe that would be consistent with standard SQL databases. When translating
stable-sorting dialects to rel nodes, we can use a rule that pushes sorts to
the outermost layer possible, similar to the example I gave in my last comment.
# The canonical behavior of Calcite's rel nodes *could* depart from what a
"standard SQL" dialect expects, and keep prior sorts stable. When translating
standard SQL dialects into rel nodes, we can use a rule that removes
unrestricted inner sorts, so that translating the resulting rel node into a
stable-sorting dialect produces the same results.
No matter which is chosen, both styles should be accommodable in any dialect. I
have a feeling these rules I'm describing may already exist.
> 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)
