You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by gu...@apache.org on 2024/01/24 09:51:55 UTC
(calcite) branch main updated: [CALCITE-6213] The default behavior of NullCollation in Presto is LAST
This is an automated email from the ASF dual-hosted git repository.
guohongyu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new aca7f02dfd [CALCITE-6213] The default behavior of NullCollation in Presto is LAST
aca7f02dfd is described below
commit aca7f02dfd510297bd56b07ab94d41033a5146b4
Author: yiwenwu <yi...@tencent.com>
AuthorDate: Sat Jan 20 00:08:58 2024 +0800
[CALCITE-6213] The default behavior of NullCollation in Presto is LAST
---
.../java/org/apache/calcite/sql/SqlDialect.java | 2 +-
.../calcite/sql/dialect/PrestoSqlDialect.java | 2 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 100 ++++++++++++++++++++-
3 files changed, 99 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 71157191e8..a013c861e5 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -1351,7 +1351,7 @@ public class SqlDialect {
INTERBASE("Interbase", null, NullCollation.HIGH),
PHOENIX("Phoenix", "\"", NullCollation.HIGH),
POSTGRESQL("PostgreSQL", "\"", NullCollation.HIGH),
- PRESTO("Presto", "\"", NullCollation.LOW),
+ PRESTO("Presto", "\"", NullCollation.LAST),
NETEZZA("Netezza", "\"", NullCollation.HIGH),
INFOBRIGHT("Infobright", "`", NullCollation.HIGH),
NEOVIEW("Neoview", null, NullCollation.HIGH),
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
index 4209fe77bf..de80351c3d 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/PrestoSqlDialect.java
@@ -41,7 +41,7 @@ public class PrestoSqlDialect extends SqlDialect {
.withDatabaseProduct(DatabaseProduct.PRESTO)
.withIdentifierQuoteString("\"")
.withUnquotedCasing(Casing.UNCHANGED)
- .withNullCollation(NullCollation.LOW);
+ .withNullCollation(NullCollation.LAST);
public static final SqlDialect DEFAULT = new PrestoSqlDialect(DEFAULT_CONTEXT);
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 258aca0efa..8e4fb1d082 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -973,8 +973,7 @@ class RelToSqlConverterTest {
final String expectedPresto = "SELECT \"product_class_id\", COUNT(*) AS \"C\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY ROLLUP(\"product_class_id\")\n"
- + "ORDER BY \"product_class_id\" IS NULL, \"product_class_id\", "
- + "COUNT(*) IS NULL, 2";
+ + "ORDER BY \"product_class_id\", 2";
sql(query)
.ok(expected)
.withMysql().ok(expectedMysql)
@@ -2081,7 +2080,7 @@ class RelToSqlConverterTest {
final String prestoExpected = "SELECT \"product_id\", COUNT(*) AS \"c\"\n"
+ "FROM \"foodmart\".\"product\"\n"
+ "GROUP BY \"product_id\"\n"
- + "ORDER BY COUNT(*) IS NULL, 2";
+ + "ORDER BY 2";
sql(query)
.ok(ordinalExpected)
.dialect(nonOrdinalDialect())
@@ -7726,6 +7725,101 @@ class RelToSqlConverterTest {
sql(query).withSpark().withLibrary(SqlLibrary.SPARK).ok(expectedSql);
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-6213">[CALCITE-6213]
+ * The default behavior of NullCollation in Presto is LAST </a>.
+ */
+ @Test void testNullCollation() {
+ final String query = "select * from \"product\" order by \"brand_name\"";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\"";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name NULLS LAST";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
+ @Test void testNullCollationAsc() {
+ final String query = "select * from \"product\" order by \"brand_name\" asc";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\"";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name NULLS LAST";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
+ @Test void testNullCollationAscNullLast() {
+ final String query = "select * from \"product\" order by \"brand_name\" asc nulls last";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\"";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name NULLS LAST";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
+ @Test void testNullCollationAscNullFirst() {
+ final String query = "select * from \"product\" order by \"brand_name\" asc nulls first";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\" IS NULL DESC, \"brand_name\"";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
+ @Test void testNullCollationDesc() {
+ final String query = "select * from \"product\" order by \"brand_name\" desc";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\" IS NULL DESC, \"brand_name\" DESC";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name DESC NULLS FIRST";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
+ @Test void testNullCollationDescLast() {
+ final String query = "select * from \"product\" order by \"brand_name\" desc nulls last";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\" DESC";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name DESC";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
+ @Test void testNullCollationDescFirst() {
+ final String query = "select * from \"product\" order by \"brand_name\" desc nulls first";
+ final String expected = "SELECT *\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "ORDER BY \"brand_name\" IS NULL DESC, \"brand_name\" DESC";
+ final String sparkExpected = "SELECT *\n"
+ + "FROM foodmart.product\n"
+ + "ORDER BY brand_name DESC NULLS FIRST";
+ sql(query)
+ .withPresto().ok(expected)
+ .withSpark().ok(sparkExpected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;