Repository: calcite Updated Branches: refs/heads/master 17cd76af7 -> 2f0eecb3c
[CALCITE-1998] Hive ORDER BY null values (Abbas Gadhia) Make HiveSqlDialect aware of the version so that it may switch NULLS FIRST/NULLS LAST behavior based on version. Use DatabaseMetadata major and minor version integer values instead of relying on strings to compare versions. Add dialect for Google BigQuery. Change the default NullCollation for MySQL to reflect the correct collation. Optimize the code generated for NULLS FIRST/LAST based on the order of the sort. If the default sort would return nulls in the same order as the NULLS FIRST/LAST flag, then we are dropping the emulation of the NULLS FIRST/LAST clause. Change the emulateNulls method to support all types of NullCollations Fix javadoc, restore MySQL calls to ISNULL. (Julian Hyde) Close apache/calcite#545 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/45425103 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/45425103 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/45425103 Branch: refs/heads/master Commit: 45425103c0be0604bb3c6c650f8f13ecb47de519 Parents: 17cd76a Author: Abbas Gadhia <[email protected]> Authored: Thu Oct 5 16:33:31 2017 +0530 Committer: Julian Hyde <[email protected]> Committed: Thu Nov 2 11:30:26 2017 -0700 ---------------------------------------------------------------------- .../apache/calcite/config/NullCollation.java | 20 ++ .../apache/calcite/rel/RelFieldCollation.java | 12 + .../calcite/rel/rel2sql/RelToSqlConverter.java | 5 +- .../calcite/rel/rel2sql/SqlImplementor.java | 5 +- .../java/org/apache/calcite/sql/SqlDialect.java | 79 ++++++- .../calcite/sql/SqlDialectFactoryImpl.java | 9 +- .../calcite/sql/dialect/BigQuerySqlDialect.java | 39 ++++ .../calcite/sql/dialect/HiveSqlDialect.java | 23 +- .../calcite/sql/dialect/MysqlSqlDialect.java | 13 +- .../rel/rel2sql/RelToSqlConverterTest.java | 226 +++++++++++++++++++ .../java/org/apache/calcite/test/JdbcTest.java | 42 +++- 11 files changed, 434 insertions(+), 39 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/config/NullCollation.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/config/NullCollation.java b/core/src/main/java/org/apache/calcite/config/NullCollation.java index 8fc17f2..f26191b 100644 --- a/core/src/main/java/org/apache/calcite/config/NullCollation.java +++ b/core/src/main/java/org/apache/calcite/config/NullCollation.java @@ -45,6 +45,26 @@ public enum NullCollation { return !desc; } } + + /** Returns whether a given combination of null direction and sort order is + * the default order of nulls returned in the ORDER BY clause. */ + public boolean isDefaultOrder(boolean nullsFirst, boolean desc) { + final boolean asc = !desc; + final boolean nullsLast = !nullsFirst; + + switch (this) { + case FIRST: + return nullsFirst; + case LAST: + return nullsLast; + case LOW: + return (asc && nullsFirst) || (desc && nullsLast); + case HIGH: + return (asc && nullsLast) || (desc && nullsFirst); + default: + throw new IllegalArgumentException("Unrecognized Null Collation"); + } + } } // End NullCollation.java http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java b/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java index c216912..660b3cd 100644 --- a/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java +++ b/core/src/main/java/org/apache/calcite/rel/RelFieldCollation.java @@ -139,6 +139,18 @@ public class RelFieldCollation { return NullDirection.UNSPECIFIED; } } + + /** Returns whether this is {@link #DESCENDING} or + * {@link #STRICTLY_DESCENDING}. */ + public boolean isDescending() { + switch (this) { + case DESCENDING: + case STRICTLY_DESCENDING: + return true; + default: + return false; + } + } } /** http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java index 1e60c10..5e9cfc2 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java @@ -457,8 +457,9 @@ public class RelToSqlConverter extends SqlImplementor for (RelFieldCollation fc : e.getOrderKeys().getFieldCollations()) { if (fc.nullDirection != RelFieldCollation.NullDirection.UNSPECIFIED) { boolean first = fc.nullDirection == RelFieldCollation.NullDirection.FIRST; - SqlNode nullDirectionNode = dialect.emulateNullDirection( - context.field(fc.getFieldIndex()), first); + SqlNode nullDirectionNode = + dialect.emulateNullDirection(context.field(fc.getFieldIndex()), + first, fc.direction.isDescending()); if (nullDirectionNode != null) { orderBySqlList.add(nullDirectionNode); fc = new RelFieldCollation(fc.getFieldIndex(), fc.getDirection(), http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java index 0304743..0faacf5 100644 --- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java +++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java @@ -1122,8 +1122,9 @@ public abstract class SqlImplementor { RelFieldCollation field) { if (field.nullDirection != RelFieldCollation.NullDirection.UNSPECIFIED) { boolean first = field.nullDirection == RelFieldCollation.NullDirection.FIRST; - SqlNode nullDirectionNode = dialect.emulateNullDirection( - context.field(field.getFieldIndex()), first); + SqlNode nullDirectionNode = + dialect.emulateNullDirection(context.field(field.getFieldIndex()), + first, field.direction.isDescending()); if (nullDirectionNode != null) { orderByList.add(nullDirectionNode); field = new RelFieldCollation(field.getFieldIndex(), http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/sql/SqlDialect.java ---------------------------------------------------------------------- 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 6023089..e2fe25a 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -23,6 +23,7 @@ import org.apache.calcite.rel.RelFieldCollation; import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.sql.dialect.AnsiSqlDialect; import org.apache.calcite.sql.dialect.CalciteSqlDialect; +import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.parser.SqlParserPos; import org.apache.calcite.sql.type.BasicSqlType; import org.apache.calcite.sql.type.SqlTypeUtil; @@ -74,7 +75,7 @@ public class SqlDialect { private final String identifierEndQuoteString; private final String identifierEscapedQuote; private final DatabaseProduct databaseProduct; - private final NullCollation nullCollation; + protected final NullCollation nullCollation; //~ Constructors ----------------------------------------------------------- @@ -155,7 +156,7 @@ public class SqlDialect { /** Creates an empty context. Use {@link #EMPTY_CONTEXT} if possible. */ protected static Context emptyContext() { - return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, null, + return new ContextImpl(DatabaseProduct.UNKNOWN, null, null, -1, -1, null, NullCollation.HIGH); } @@ -226,6 +227,8 @@ public class SqlDialect { return DatabaseProduct.H2; } else if (upperProductName.contains("VERTICA")) { return DatabaseProduct.VERTICA; + } else if (upperProductName.contains("GOOGLE BIGQUERY")) { + return DatabaseProduct.BIG_QUERY; } else { return DatabaseProduct.UNKNOWN; } @@ -532,13 +535,32 @@ public class SqlDialect { * or <code>null</code> if no emulation needs to be done. * * @param node The SqlNode representing the expression - * @param nullsFirst <code>true</code> if nulls should come first, <code>false</code> otherwise + * @param nullsFirst Whether nulls should come first + * @param desc Whether the sort direction is + * {@link org.apache.calcite.rel.RelFieldCollation.Direction#DESCENDING} or + * {@link org.apache.calcite.rel.RelFieldCollation.Direction#STRICTLY_DESCENDING} * @return A SqlNode for null direction emulation or <code>null</code> if not required */ - public SqlNode emulateNullDirection(SqlNode node, boolean nullsFirst) { + public SqlNode emulateNullDirection(SqlNode node, boolean nullsFirst, + boolean desc) { return null; } + protected SqlNode emulateNullDirectionWithIsNull(SqlNode node, + boolean nullsFirst, boolean desc) { + // No need for emulation if the nulls will anyways come out the way we want + // them based on "nullsFirst" and "desc". + if (nullCollation.isDefaultOrder(nullsFirst, desc)) { + return null; + } + + node = SqlStdOperatorTable.IS_NULL.createCall(SqlParserPos.ZERO, node); + if (nullsFirst) { + node = SqlStdOperatorTable.DESC.createCall(SqlParserPos.ZERO, node); + } + return node; + } + /** * Returns whether the dialect supports OFFSET/FETCH clauses * introduced by SQL:2008, for instance @@ -667,15 +689,16 @@ public class SqlDialect { */ public enum DatabaseProduct { ACCESS("Access", "\"", NullCollation.HIGH), + BIG_QUERY("Google BigQuery", "`", NullCollation.LOW), CALCITE("Apache Calcite", "\"", NullCollation.HIGH), MSSQL("Microsoft SQL Server", "[", NullCollation.HIGH), - MYSQL("MySQL", "`", NullCollation.HIGH), + MYSQL("MySQL", "`", NullCollation.LOW), ORACLE("Oracle", "\"", NullCollation.HIGH), DERBY("Apache Derby", null, NullCollation.HIGH), DB2("IBM DB2", null, NullCollation.HIGH), FIREBIRD("Firebird", null, NullCollation.HIGH), H2("H2", "\"", NullCollation.HIGH), - HIVE("Apache Hive", null, NullCollation.HIGH), + HIVE("Apache Hive", null, NullCollation.LOW), INFORMIX("Informix", null, NullCollation.HIGH), INGRES("Ingres", null, NullCollation.HIGH), LUCIDDB("LucidDB", "\"", NullCollation.HIGH), @@ -760,6 +783,10 @@ public class SqlDialect { Context withDatabaseProductName(String databaseProductName); String databaseVersion(); Context withDatabaseVersion(String databaseVersion); + int databaseMajorVersion(); + Context withDatabaseMajorVersion(int databaseMajorVersion); + int databaseMinorVersion(); + Context withDatabaseMinorVersion(int databaseMinorVersion); String identifierQuoteString(); Context withIdentifierQuoteString(String identifierQuoteString); @Nonnull NullCollation nullCollation(); @@ -771,15 +798,20 @@ public class SqlDialect { private final DatabaseProduct databaseProduct; private final String databaseProductName; private final String databaseVersion; + private final int databaseMajorVersion; + private final int databaseMinorVersion; private final String identifierQuoteString; private final NullCollation nullCollation; private ContextImpl(DatabaseProduct databaseProduct, String databaseProductName, String databaseVersion, + int databaseMajorVersion, int databaseMinorVersion, String identifierQuoteString, NullCollation nullCollation) { this.databaseProduct = Preconditions.checkNotNull(databaseProduct); this.databaseProductName = databaseProductName; this.databaseVersion = databaseVersion; + this.databaseMajorVersion = databaseMajorVersion; + this.databaseMinorVersion = databaseMinorVersion; this.identifierQuoteString = identifierQuoteString; this.nullCollation = Preconditions.checkNotNull(nullCollation); } @@ -791,7 +823,8 @@ public class SqlDialect { public Context withDatabaseProduct( @Nonnull DatabaseProduct databaseProduct) { return new ContextImpl(databaseProduct, databaseProductName, - databaseVersion, identifierQuoteString, nullCollation); + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); } public String databaseProductName() { @@ -800,7 +833,8 @@ public class SqlDialect { public Context withDatabaseProductName(String databaseProductName) { return new ContextImpl(databaseProduct, databaseProductName, - databaseVersion, identifierQuoteString, nullCollation); + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); } public String databaseVersion() { @@ -809,7 +843,28 @@ public class SqlDialect { public Context withDatabaseVersion(String databaseVersion) { return new ContextImpl(databaseProduct, databaseProductName, - databaseVersion, identifierQuoteString, nullCollation); + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); + } + + public int databaseMajorVersion() { + return databaseMajorVersion; + } + + public Context withDatabaseMajorVersion(int databaseMajorVersion) { + return new ContextImpl(databaseProduct, databaseProductName, + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); + } + + public int databaseMinorVersion() { + return databaseMinorVersion; + } + + public Context withDatabaseMinorVersion(int databaseMinorVersion) { + return new ContextImpl(databaseProduct, databaseProductName, + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); } public String identifierQuoteString() { @@ -818,7 +873,8 @@ public class SqlDialect { public Context withIdentifierQuoteString(String identifierQuoteString) { return new ContextImpl(databaseProduct, databaseProductName, - databaseVersion, identifierQuoteString, nullCollation); + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); } @Nonnull public NullCollation nullCollation() { @@ -827,7 +883,8 @@ public class SqlDialect { public Context withNullCollation(@Nonnull NullCollation nullCollation) { return new ContextImpl(databaseProduct, databaseProductName, - databaseVersion, identifierQuoteString, nullCollation); + databaseVersion, databaseMajorVersion, databaseMinorVersion, + identifierQuoteString, nullCollation); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java index 0457735..c2bf4e3 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialectFactoryImpl.java @@ -54,10 +54,12 @@ import java.util.Locale; public class SqlDialectFactoryImpl implements SqlDialectFactory { public SqlDialect create(DatabaseMetaData databaseMetaData) { String databaseProductName; - String databaseVersion; + int databaseMajorVersion; + int databaseMinorVersion; try { databaseProductName = databaseMetaData.getDatabaseProductName(); - databaseVersion = databaseMetaData.getDatabaseProductVersion(); + databaseMajorVersion = databaseMetaData.getDatabaseMajorVersion(); + databaseMinorVersion = databaseMetaData.getDatabaseMinorVersion(); } catch (SQLException e) { throw new RuntimeException("while detecting database product", e); } @@ -67,7 +69,8 @@ public class SqlDialectFactoryImpl implements SqlDialectFactory { final NullCollation nullCollation = getNullCollation(databaseMetaData); final SqlDialect.Context c = SqlDialect.EMPTY_CONTEXT .withDatabaseProductName(databaseProductName) - .withDatabaseVersion(databaseVersion) + .withDatabaseMajorVersion(databaseMajorVersion) + .withDatabaseMinorVersion(databaseMinorVersion) .withIdentifierQuoteString(quoteString) .withNullCollation(nullCollation); switch (upperProductName) { http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java new file mode 100644 index 0000000..b983005 --- /dev/null +++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java @@ -0,0 +1,39 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.calcite.sql.dialect; + +import org.apache.calcite.config.NullCollation; +import org.apache.calcite.sql.SqlDialect; + +/** + * A <code>SqlDialect</code> implementation for Google BigQuery's "Standard SQL" + * dialect. + */ +public class BigQuerySqlDialect extends SqlDialect { + public static final SqlDialect DEFAULT = + new BigQuerySqlDialect( + EMPTY_CONTEXT + .withDatabaseProduct(SqlDialect.DatabaseProduct.BIG_QUERY) + .withNullCollation(NullCollation.LOW)); + + /** Creates a BigQuerySqlDialect. */ + public BigQuerySqlDialect(SqlDialect.Context context) { + super(context); + } +} + +// End BigQuerySqlDialect.java http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java index 52f0a81..506c40b 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/HiveSqlDialect.java @@ -16,19 +16,29 @@ */ package org.apache.calcite.sql.dialect; +import org.apache.calcite.config.NullCollation; import org.apache.calcite.sql.SqlDialect; +import org.apache.calcite.sql.SqlNode; /** * A <code>SqlDialect</code> implementation for the Apache Hive database. */ public class HiveSqlDialect extends SqlDialect { public static final SqlDialect DEFAULT = - new HiveSqlDialect( - EMPTY_CONTEXT.withDatabaseProduct(DatabaseProduct.HIVE)); + new HiveSqlDialect(EMPTY_CONTEXT + .withDatabaseProduct(DatabaseProduct.HIVE) + .withNullCollation(NullCollation.LOW)); + + private final boolean emulateNullDirection; /** Creates a HiveSqlDialect. */ public HiveSqlDialect(Context context) { super(context); + // Since 2.1.0, Hive natively supports "NULLS FIRST" and "NULLS LAST". + // See https://issues.apache.org/jira/browse/HIVE-12994. + emulateNullDirection = (context.databaseMajorVersion() < 2) + || (context.databaseMajorVersion() == 2 + && context.databaseMinorVersion() < 1); } @Override protected boolean allowsAs() { @@ -38,6 +48,15 @@ public class HiveSqlDialect extends SqlDialect { @Override public boolean supportsOffsetFetch() { return false; } + + @Override public SqlNode emulateNullDirection(SqlNode node, + boolean nullsFirst, boolean desc) { + if (emulateNullDirection) { + return emulateNullDirectionWithIsNull(node, nullsFirst, desc); + } + + return null; + } } // End HiveSqlDialect.java http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java index e9ae479..4038f9e 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.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.RelDataType; import org.apache.calcite.sql.SqlBasicCall; import org.apache.calcite.sql.SqlCall; @@ -45,7 +46,8 @@ public class MysqlSqlDialect extends SqlDialect { public static final SqlDialect DEFAULT = new MysqlSqlDialect(EMPTY_CONTEXT .withDatabaseProduct(DatabaseProduct.MYSQL) - .withIdentifierQuoteString("`")); + .withIdentifierQuoteString("`") + .withNullCollation(NullCollation.LOW)); /** MySQL specific function. */ public static final SqlFunction ISNULL_FUNCTION = @@ -66,12 +68,9 @@ public class MysqlSqlDialect extends SqlDialect { return false; } - @Override public SqlNode emulateNullDirection(SqlNode node, boolean nullsFirst) { - node = ISNULL_FUNCTION.createCall(SqlParserPos.ZERO, node); - if (nullsFirst) { - node = SqlStdOperatorTable.DESC.createCall(SqlParserPos.ZERO, node); - } - return node; + @Override public SqlNode emulateNullDirection(SqlNode node, + boolean nullsFirst, boolean desc) { + return emulateNullDirectionWithIsNull(node, nullsFirst, desc); } @Override public boolean supportsAggregateFunction(SqlKind kind) { http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java ---------------------------------------------------------------------- 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 a8682f8..d89df0b 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 @@ -16,6 +16,7 @@ */ package org.apache.calcite.rel.rel2sql; +import org.apache.calcite.config.NullCollation; import org.apache.calcite.plan.RelOptLattice; import org.apache.calcite.plan.RelOptMaterialization; import org.apache.calcite.plan.RelOptPlanner; @@ -30,6 +31,8 @@ import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.dialect.CalciteSqlDialect; +import org.apache.calcite.sql.dialect.HiveSqlDialect; +import org.apache.calcite.sql.dialect.MysqlSqlDialect; import org.apache.calcite.sql.parser.SqlParser; import org.apache.calcite.sql2rel.SqlToRelConverter; import org.apache.calcite.test.CalciteAssert; @@ -92,6 +95,13 @@ public class RelToSqlConverterTest { return Frameworks.getPlanner(config); } + private static MysqlSqlDialect mySqlDialect(NullCollation nullCollation) { + return new MysqlSqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseProduct(SqlDialect.DatabaseProduct.MYSQL) + .withIdentifierQuoteString("`") + .withNullCollation(nullCollation)); + } + @Test public void testSimpleSelectStarFromProductTable() { String query = "select * from \"product\""; sql(query).ok("SELECT *\nFROM \"foodmart\".\"product\""); @@ -340,6 +350,222 @@ public class RelToSqlConverterTest { sql(query).withHive().ok(expected); } + @Test public void testHiveSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + 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); + } + + @Test public void testHiveSelectQueryWithOrderByAscAndNullsLastShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls last"; + 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); + } + + @Test public void testHiveSelectQueryWithOrderByAscNullsFirstShouldNotAddNullEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls first"; + final String expected = "SELECT product_id\n" + + "FROM foodmart.product\n" + + "ORDER BY product_id"; + sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); + } + + @Test public void testHiveSelectQueryWithOrderByDescNullsLastShouldNotAddNullEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls last"; + final String expected = "SELECT product_id\n" + + "FROM foodmart.product\n" + + "ORDER BY product_id DESC"; + sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); + } + + @Test public void testHiveSelectQueryWithOrderByDescAndHighNullsWithVersionGreaterThanOrEq21() { + final HiveSqlDialect hive2_1Dialect = + new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseMajorVersion(2) + .withDatabaseMinorVersion(1) + .withNullCollation(NullCollation.LOW)); + + final HiveSqlDialect hive2_2_Dialect = + new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseMajorVersion(2) + .withDatabaseMinorVersion(2) + .withNullCollation(NullCollation.LOW)); + + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + final String expected = "SELECT product_id\n" + + "FROM foodmart.product\n" + + "ORDER BY product_id DESC NULLS FIRST"; + sql(query).dialect(hive2_1Dialect).ok(expected); + sql(query).dialect(hive2_2_Dialect).ok(expected); + } + + @Test public void testHiveSelectQueryWithOrderByDescAndHighNullsWithVersion20() { + final HiveSqlDialect hive2_1_0_Dialect = + new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseMajorVersion(2) + .withDatabaseMinorVersion(0) + .withNullCollation(NullCollation.LOW)); + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + final String expected = "SELECT product_id\n" + + "FROM foodmart.product\n" + + "ORDER BY product_id IS NULL DESC, product_id DESC"; + sql(query).dialect(hive2_1_0_Dialect).ok(expected); + } + + @Test public void testMySqlSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + 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); + } + + @Test public void testMySqlSelectQueryWithOrderByAscAndNullsLastShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls last"; + 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); + } + + @Test public void testMySqlSelectQueryWithOrderByAscNullsFirstShouldNotAddNullEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id`"; + sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); + } + + @Test public void testMySqlSelectQueryWithOrderByDescNullsLastShouldNotAddNullEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` DESC"; + sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); + } + + @Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsLastAndNoEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id`"; + sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); + } + + @Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsFirstAndNullEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL DESC, `product_id`"; + sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); + } + + @Test public void testMySqlWithHighNullsSelectWithOrderByDescNullsFirstAndNoEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` DESC"; + sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); + } + + @Test public void testMySqlWithHighNullsSelectWithOrderByDescNullsLastAndNullEmulation() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL, `product_id` DESC"; + sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); + } + + @Test public void testMySqlWithFirstNullsSelectWithOrderByDescAndNullsFirstShouldNotBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` DESC"; + sql(query).dialect(mySqlDialect(NullCollation.FIRST)).ok(expected); + } + + @Test public void testMySqlWithFirstNullsSelectWithOrderByAscAndNullsFirstShouldNotBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id`"; + sql(query).dialect(mySqlDialect(NullCollation.FIRST)).ok(expected); + } + + @Test public void testMySqlWithFirstNullsSelectWithOrderByDescAndNullsLastShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL, `product_id` DESC"; + sql(query).dialect(mySqlDialect(NullCollation.FIRST)).ok(expected); + } + + @Test public void testMySqlWithFirstNullsSelectWithOrderByAscAndNullsLastShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL, `product_id`"; + sql(query).dialect(mySqlDialect(NullCollation.FIRST)).ok(expected); + } + + @Test public void testMySqlWithLastNullsSelectWithOrderByDescAndNullsFirstShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL DESC, `product_id` DESC"; + sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected); + } + + @Test public void testMySqlWithLastNullsSelectWithOrderByAscAndNullsFirstShouldBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls first"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` IS NULL DESC, `product_id`"; + sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected); + } + + @Test public void testMySqlWithLastNullsSelectWithOrderByDescAndNullsLastShouldNotBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" desc nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id` DESC"; + sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected); + } + + @Test public void testMySqlWithLastNullsSelectWithOrderByAscAndNullsLastShouldNotBeEmulated() { + final String query = "select \"product_id\" from \"product\"\n" + + "order by \"product_id\" nulls last"; + final String expected = "SELECT `product_id`\n" + + "FROM `foodmart`.`product`\n" + + "ORDER BY `product_id`"; + sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected); + } + @Test public void testSelectQueryWithLimitClauseWithoutOrder() { String query = "select \"product_id\" from \"product\" limit 100 offset 10"; final String expected = "SELECT \"product_id\"\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/45425103/core/src/test/java/org/apache/calcite/test/JdbcTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java index 4c0975c..af69242 100644 --- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java +++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java @@ -2853,11 +2853,26 @@ public class JdbcTest { + "customer_id=1; postal_code=15057\n"); } + /** Tests ORDER BY with all combinations of ASC, DESC, NULLS FIRST, + * NULLS LAST. */ + @Test public void testOrderByNulls() { + checkOrderByNulls(CalciteAssert.Config.FOODMART_CLONE); + checkOrderByNulls(CalciteAssert.Config.JDBC_FOODMART); + } + + private void checkOrderByNulls(CalciteAssert.Config clone) { + checkOrderByDescNullsFirst(clone); + checkOrderByNullsFirst(clone); + checkOrderByDescNullsLast(clone); + checkOrderByNullsLast(clone); + } + /** Tests ORDER BY ... DESC NULLS FIRST. */ - @Test public void testOrderByDescNullsFirst() { + private void checkOrderByDescNullsFirst(CalciteAssert.Config config) { CalciteAssert.that() - .with(CalciteAssert.Config.FOODMART_CLONE) - .query("select \"store_id\", \"grocery_sqft\" from \"store\"\n" + .with(config) + .query("select \"store_id\", \"grocery_sqft\"\n" + + "from \"foodmart\".\"store\"\n" + "where \"store_id\" < 3 order by 2 desc nulls first") .returns("store_id=0; grocery_sqft=null\n" + "store_id=2; grocery_sqft=22271\n" @@ -2865,10 +2880,11 @@ public class JdbcTest { } /** Tests ORDER BY ... NULLS FIRST. */ - @Test public void testOrderByNullsFirst() { + private void checkOrderByNullsFirst(CalciteAssert.Config config) { CalciteAssert.that() - .with(CalciteAssert.Config.FOODMART_CLONE) - .query("select \"store_id\", \"grocery_sqft\" from \"store\"\n" + .with(config) + .query("select \"store_id\", \"grocery_sqft\"\n" + + "from \"foodmart\".\"store\"\n" + "where \"store_id\" < 3 order by 2 nulls first") .returns("store_id=0; grocery_sqft=null\n" + "store_id=1; grocery_sqft=17475\n" @@ -2876,10 +2892,11 @@ public class JdbcTest { } /** Tests ORDER BY ... DESC NULLS LAST. */ - @Test public void testOrderByDescNullsLast() { + private void checkOrderByDescNullsLast(CalciteAssert.Config config) { CalciteAssert.that() - .with(CalciteAssert.Config.FOODMART_CLONE) - .query("select \"store_id\", \"grocery_sqft\" from \"store\"\n" + .with(config) + .query("select \"store_id\", \"grocery_sqft\"\n" + + "from \"foodmart\".\"store\"\n" + "where \"store_id\" < 3 order by 2 desc nulls last") .returns("store_id=2; grocery_sqft=22271\n" + "store_id=1; grocery_sqft=17475\n" @@ -2887,10 +2904,11 @@ public class JdbcTest { } /** Tests ORDER BY ... NULLS LAST. */ - @Test public void testOrderByNullsLast() { + private void checkOrderByNullsLast(CalciteAssert.Config config) { CalciteAssert.that() - .with(CalciteAssert.Config.FOODMART_CLONE) - .query("select \"store_id\", \"grocery_sqft\" from \"store\"\n" + .with(config) + .query("select \"store_id\", \"grocery_sqft\"\n" + + "from \"foodmart\".\"store\"\n" + "where \"store_id\" < 3 order by 2 nulls last") .returns("store_id=1; grocery_sqft=17475\n" + "store_id=2; grocery_sqft=22271\n"
