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)
