[
https://issues.apache.org/jira/browse/CALCITE-6798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17916641#comment-17916641
]
suibianwanwan commented on CALCITE-6798:
----------------------------------------
Hi, [~VAE]. I don't think it's a bug, but something to do with the use of
SqlDialect. NullCollation is determined at the validate stage. If you enter a
Presto syntax and return the MySQL dialect. The result produced by the Presto
semantics should be returned. So if you use MySQL's NullCollation configuration
in validator, you will get the results you expect.
> 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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
