[
https://issues.apache.org/jira/browse/CALCITE-6798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ZheHu updated CALCITE-6798:
---------------------------
Description:
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}
was:
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(MysqlSqlDialect.DEFAULT).ok(expected);
}
{code}
> 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)
