This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 0c8cbacd0f30b5df15b7cb93c0ab2be5d0324372 Author: Justin Swett <[email protected]> AuthorDate: Tue Sep 24 15:40:53 2019 -0700 [CALCITE-3370] In JDBC adapter for Microsoft SQL Server, emulate NULLS FIRST using CASE expression (Justin Swett) Also, remove whitespace from README.md Close apache/calcite#1474 --- README.md | 8 ++-- .../calcite/sql/dialect/MssqlSqlDialect.java | 50 +++++++++++++++++++++- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 46 +++++++++++++++----- 3 files changed, 89 insertions(+), 15 deletions(-) diff --git a/README.md b/README.md index 7a0f971..a9cfee9 100644 --- a/README.md +++ b/README.md @@ -25,12 +25,12 @@ Apache Calcite is a dynamic data management framework. It contains many of the pieces that comprise a typical database management system but omits the storage primitives. -It provides an industry standard SQL parser and validator, +It provides an industry standard SQL parser and validator, a customisable optimizer with pluggable rules and cost functions, logical and physical algebraic operators, various transformation algorithms from SQL to algebra (and the opposite), and many -adapters for executing SQL queries over Cassandra, Druid, -Elasticsearch, MongoDB, Kafka, and others, with minimal -configuration. +adapters for executing SQL queries over Cassandra, Druid, +Elasticsearch, MongoDB, Kafka, and others, with minimal +configuration. For more details, see the [home page](http://calcite.apache.org). diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java index 0eaf814..62461b1 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java @@ -17,6 +17,7 @@ package org.apache.calcite.sql.dialect; import org.apache.calcite.avatica.util.TimeUnitRange; +import org.apache.calcite.config.NullCollation; import org.apache.calcite.rel.type.RelDataTypeSystem; import org.apache.calcite.sql.SqlAbstractDateTimeLiteral; import org.apache.calcite.sql.SqlCall; @@ -28,9 +29,11 @@ import org.apache.calcite.sql.SqlIntervalQualifier; import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlLiteral; import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlNodeList; import org.apache.calcite.sql.SqlUtil; import org.apache.calcite.sql.SqlWriter; import org.apache.calcite.sql.fun.SqlStdOperatorTable; +import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.ReturnTypes; /** @@ -42,7 +45,8 @@ public class MssqlSqlDialect extends SqlDialect { new MssqlSqlDialect(EMPTY_CONTEXT .withDatabaseProduct(DatabaseProduct.MSSQL) .withIdentifierQuoteString("[") - .withCaseSensitive(false)); + .withCaseSensitive(false) + .withNullCollation(NullCollation.LOW)); private static final SqlFunction MSSQL_SUBSTRING = new SqlFunction("SUBSTRING", SqlKind.OTHER_FUNCTION, @@ -61,6 +65,50 @@ public class MssqlSqlDialect extends SqlDialect { top = context.databaseMajorVersion() < 11; } + /** {@inheritDoc} + * + * <p>MSSQL does not support NULLS FIRST, so we emulate using CASE + * expressions. For example, + * + * <blockquote>{@code ORDER BY x NULLS FIRST}</blockquote> + * + * <p>becomes + * + * <blockquote> + * {@code ORDER BY CASE WHEN x IS NULL THEN 0 ELSE 1 END, x} + * </blockquote> + */ + @Override public SqlNode emulateNullDirection(SqlNode node, + boolean nullsFirst, boolean desc) { + // Default ordering preserved + if (nullCollation.isDefaultOrder(nullsFirst, desc)) { + return null; + } + + // Grouping node should preserve grouping, no emulation needed + if (node.getKind() == SqlKind.GROUPING) { + return node; + } + + // Emulate nulls first/last with case ordering + final SqlParserPos pos = SqlParserPos.ZERO; + final SqlNodeList whenList = + SqlNodeList.of(SqlStdOperatorTable.IS_NULL.createCall(pos, node)); + + final SqlNode oneLiteral = SqlLiteral.createExactNumeric("1", pos); + final SqlNode zeroLiteral = SqlLiteral.createExactNumeric("0", pos); + + if (nullsFirst) { + // IS NULL THEN 0 ELSE 1 END + return SqlStdOperatorTable.CASE.createCall(null, pos, + null, whenList, SqlNodeList.of(zeroLiteral), oneLiteral); + } else { + // IS NULL THEN 1 ELSE 0 END + return SqlStdOperatorTable.CASE.createCall(null, pos, + null, whenList, SqlNodeList.of(oneLiteral), zeroLiteral); + } + } + @Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset, SqlNode fetch) { if (!top) { 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 e05656f..a4185b2 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 @@ -1031,40 +1031,66 @@ public class RelToSqlConverterTest { sql(query).withBigQuery().ok(expected); } - @Test public void testHiveSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { + @Test public void testSelectOrderByDescNullsFirst() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; + // Hive and MSSQL do not support NULLS FIRST, so need to emulate final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id IS NULL DESC, product_id DESC"; - sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); + final String mssqlExpected = "SELECT [product_id]\n" + + "FROM [foodmart].[product]\n" + + "ORDER BY CASE WHEN [product_id] IS NULL THEN 0 ELSE 1 END, [product_id] DESC"; + sql(query) + .dialect(HiveSqlDialect.DEFAULT).ok(expected) + .dialect(MssqlSqlDialect.DEFAULT).ok(mssqlExpected); } - @Test public void testHiveSelectQueryWithOrderByAscAndNullsLastShouldBeEmulated() { + @Test public void testSelectOrderByAscNullsLast() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls last"; + // Hive and MSSQL do not support NULLS LAST, so need to emulate final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id IS NULL, product_id"; - sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); + final String mssqlExpected = "SELECT [product_id]\n" + + "FROM [foodmart].[product]\n" + + "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END, [product_id]"; + sql(query) + .dialect(HiveSqlDialect.DEFAULT).ok(expected) + .dialect(MssqlSqlDialect.DEFAULT).ok(mssqlExpected); } - @Test public void testHiveSelectQueryWithOrderByAscNullsFirstShouldNotAddNullEmulation() { + @Test public void testSelectOrderByAscNullsFirst() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; + // Hive and MSSQL do not support NULLS FIRST, but nulls sort low, so no + // need to emulate final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id"; - sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); + final String mssqlExpected = "SELECT [product_id]\n" + + "FROM [foodmart].[product]\n" + + "ORDER BY [product_id]"; + sql(query) + .dialect(HiveSqlDialect.DEFAULT).ok(expected) + .dialect(MssqlSqlDialect.DEFAULT).ok(mssqlExpected); } - @Test public void testHiveSelectQueryWithOrderByDescNullsLastShouldNotAddNullEmulation() { + @Test public void testSelectOrderByDescNullsLast() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls last"; + // Hive and MSSQL do not support NULLS LAST, but nulls sort low, so no + // need to emulate final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id DESC"; - sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); + final String mssqlExpected = "SELECT [product_id]\n" + + "FROM [foodmart].[product]\n" + + "ORDER BY [product_id] DESC"; + sql(query) + .dialect(HiveSqlDialect.DEFAULT).ok(expected) + .dialect(MssqlSqlDialect.DEFAULT).ok(mssqlExpected); } @Test public void testHiveSubstring() { @@ -1368,10 +1394,10 @@ public class RelToSqlConverterTest { + "FETCH NEXT 100 ROWS ONLY"; final String expectedMssql10 = "SELECT TOP (100) [product_id]\n" + "FROM [foodmart].[product]\n" - + "ORDER BY [product_id]"; + + "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END, [product_id]"; final String expectedMssql = "SELECT [product_id]\n" + "FROM [foodmart].[product]\n" - + "ORDER BY [product_id]\n" + + "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END, [product_id]\n" + "FETCH NEXT 100 ROWS ONLY"; final String expectedSybase = "SELECT TOP (100) product_id\n" + "FROM foodmart.product\n"
