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

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

ZheHu created CALCITE-6798:
------------------------------

             Summary: 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


The following test passes in RelToSqlConvertTest.
{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.



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