[jira] [Commented] (CALCITE-4901) RelToSqlConverter adds redundant order by output column for the result sql

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

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

Julian Hyde commented on CALCITE-4901:
--------------------------------------

This is very likely due to null sorting. E.g. the source dialect is implicitly NULLS LAST abs the target dialect is implicitly NULLS FIRST, does not have syntax for NULLS LAST, and so to achieve the semantics of the original query Calcite generates an extra ORDER BY key to handle NULL values. If so this is not a bug. 

> RelToSqlConverter adds redundant order by output column for the result sql
> --------------------------------------------------------------------------
>
>                 Key: CALCITE-4901
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4901
>             Project: Calcite
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 1.26.0
>         Environment: jdk8
>            Reporter: yanjing.wang
>            Priority: Major
>
> In RelToSqlConverterTest, I add the following case
> {code:java}
> @Test void testOrderByNotInSelectList() {
>   String query = "select count(1)\n"
>       + "from \"foodmart\".\"product\"\n"
>       + "group by \"product_id\"\n"
>       + "order by \"product_id\" desc\n";
>   final String expected = "SELECT COUNT(1)\n"
>       + "FROM \"foodmart\".\"product\"\n"
>       + "GROUP BY \"product_id\"\n"
>       + "ORDER BY \"product_id\" desc\n";
>   sql(query).withBigQuery().ok(expected);
> }{code}
> But I get the actual sql is
> {code:java}
> SELECT COUNT(*), product_id
> FROM foodmart.product
> GROUP BY product_id
> ORDER BY product_id IS NULL DESC, product_id DESC {code}
> It has redundant product_id output column.
>  
> Wage Through code, I find that the sort operator contains product_id. When I put a project with product_id only on the sort operator, the result sql has redundant subquery.
> {code:java}
> LogicalProject(EXPR$0=[$0])
>   LogicalSort(sort0=[$1], dir0=[DESC])
>     LogicalProject(EXPR$0=[$1], product_id=[$0])
>       LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
>         LogicalProject(product_id=[$1])
>           JdbcTableScan(table=[[foodmart, product]]) {code}
> {code:java}
> SELECT `EXPR$0`
> FROM (SELECT COUNT(*) AS `EXPR$0`, product_id
> FROM foodmart.product
> GROUP BY product_id
> ORDER BY product_id IS NULL DESC, product_id DESC) AS t2 {code}
>  
>  



--
This message was sent by Atlassian Jira
(v8.20.1#820001)