[jira] [Commented] (CALCITE-5724) Generated SQL uses literal values in ORDER BY clauses

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5724?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17726388#comment-17726388 ] 

Julian Hyde commented on CALCITE-5724:
--------------------------------------

There's a planner rule to remove constant keys from the {{ORDER BY}} clause. Maybe apply that?

Your proposed fix isn't great because it adds an item to the {{SELECT}} clause. They wanted a query with N {{SELECT}} items and you have generated a query with N + 1.

> Generated SQL uses literal values in ORDER BY clauses
> -----------------------------------------------------
>
>                 Key: CALCITE-5724
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5724
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Joey Moore
>            Assignee: Joey Moore
>            Priority: Major
>
> Current behavior in the SqlImplementor will generate SqlCharStringLiterals in ORDER BY fields when there is a Literal value in the SELECT clause. This happens in languages with isSortByOrdinal(). This leads to errors in dialects in which cannot have literal values in ORDER BY clauses such as BigQuery. Proposed fix is to use ordinals in all cases where a literal value is present in the SELECT clause.
> Example of current implementation:
> {code:java}
> select 3.14159265 as pi 
> from \"product\"
> order by 1;
> {code}
> Will return 
> {code:java}
> SELECT 3.14159265 AS \"PI\"
> FROM \"foodmart\".\"product\"
> ORDER BY '3.14159265'{code}
> Proposed implementation will return :
> {code:java}
> SELECT 3.14159265 AS \"PI\"
> FROM \"foodmart\".\"product\"
> ORDER BY 1{code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)