[
https://issues.apache.org/jira/browse/CALCITE-6798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17916792#comment-17916792
]
Julian Hyde commented on CALCITE-6798:
--------------------------------------
I, too, am skeptical that this is a bug. In RelToSqlConverterTest the initial
SQL query uses Calcite syntax and semantics. Because the purpose of the test is
to test translation from RelNode. The query is just a concise way to create
that RelNode. Feel free to add “NULLS FIRST” if it makes it clearer what that
RelNode is.
Also remember that the semantics of RelNodes are absolute. They are not
affected by dialects or other flags.
> 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 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 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(MysqlSqlDialect.DEFAULT).ok(expected);
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
