[jira] [Resolved] (CALCITE-6798) Null direction emulation in MySQL and Hive is incorrect when null direction is not specified

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

     [ https://issues.apache.org/jira/browse/CALCITE-6798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ZheHu resolved CALCITE-6798.
----------------------------
    Resolution: Not A Bug

> Null direction emulation in MySQL and Hive is incorrect when null direction is not specified
> --------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-6798
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6798
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.38.0
>            Reporter: ZheHu
>            Assignee: ZheHu
>            Priority: Major
>
> The following test passes in {{RelToSqlConverterTest}}.
> {code:java}
> @Test void testMySqlSelectQueryWithAscWithoutNullsDirection() {
>     final String query = "select \"product_id\" from \"product\"\n"
>         + "order by \"product_id\" asc";
>     final String expected = "SELECT `product_id`\n"
>         + "FROM `foodmart`.`product`\n"
>         + "ORDER BY `product_id` IS NULL, `product_id`";
>     sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected);
>   }
> {code}
> According to [mysql-doc|https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html], NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC, and [default |https://dev.mysql.com/doc/refman/8.0/en/sorting-rows.html]sort order is ascending. Hence, the query after dialect convert will result in NULL values presented last if you do ORDER BY ... ASC and first if you do ORDER BY ... DESC.
> HiveSqlDialect has the same problem with MySQL.
> PrestoSqlDialect has NullCollation.LAST strategy, the following query with DESC sort after dialect will make NULL values presented first (asc works fine).
> {code:java}
> @Test void testPrestoSelectQueryWithDescWithoutNullsDirection() {
>     final String query = "select \"product_id\" from \"product\"\n"
>         + "order by \"product_id\" desc";
>     final String expected = "SELECT `product_id`\n"
>         + "FROM `foodmart`.`product`\n"
>         + "ORDER BY `product_id` IS NULL DESC, `product_id` DESC";
>     sql(query).dialect(PrestoSqlDialect.DEFAULT).ok(expected);
>   }
> {code}



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