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 8b9618fdd42daa14524e8baa69124982f409937a Author: Julian Hyde <[email protected]> AuthorDate: Mon Dec 28 13:26:41 2020 -0800 [CALCITE-4449] Generate nicer SQL for Sarg 'x IS NULL OR x NOT IN (1, 2)' Close apache/calcite#2306 --- .../apache/calcite/rel/rel2sql/SqlImplementor.java | 45 ++++++++++++++-------- .../java/org/apache/calcite/sql/SqlDialect.java | 1 - .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 30 +++++++++++++++ 3 files changed, 60 insertions(+), 16 deletions(-) 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 17a5a78..ff00cf7 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 @@ -52,6 +52,7 @@ import org.apache.calcite.rex.RexWindowBound; import org.apache.calcite.sql.JoinType; import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.sql.SqlBasicCall; +import org.apache.calcite.sql.SqlBinaryOperator; import org.apache.calcite.sql.SqlCall; import org.apache.calcite.sql.SqlDialect; import org.apache.calcite.sql.SqlDynamicParam; @@ -91,6 +92,7 @@ import com.google.common.collect.ImmutableSet; import com.google.common.collect.Iterables; import com.google.common.collect.Lists; import com.google.common.collect.Range; +import com.google.common.collect.RangeSet; import org.checkerframework.checker.initialization.qual.UnknownInitialization; import org.checkerframework.checker.nullness.qual.Nullable; @@ -879,21 +881,16 @@ public abstract class SqlImplementor { orList.add(SqlStdOperatorTable.IS_NULL.createCall(POS, operandSql)); } if (sarg.isPoints()) { - final SqlNodeList list = sarg.rangeSet.asRanges().stream() - .map(range -> - toSql(program, - implementor().rexBuilder.makeLiteral(range.lowerEndpoint(), - type, true, true))) - .collect(SqlNode.toList()); - switch (list.size()) { - case 1: - orList.add( - SqlStdOperatorTable.EQUALS.createCall(POS, operandSql, - list.get(0))); - break; - default: - orList.add(SqlStdOperatorTable.IN.createCall(POS, operandSql, list)); - } + // generate 'x = 10' or 'x IN (10, 20, 30)' + orList.add( + toIn(operandSql, SqlStdOperatorTable.EQUALS, + SqlStdOperatorTable.IN, program, type, sarg.rangeSet)); + } else if (sarg.isComplementedPoints()) { + // generate 'x <> 10' or 'x NOT IN (10, 20, 30)' + orList.add( + toIn(operandSql, SqlStdOperatorTable.NOT_EQUALS, + SqlStdOperatorTable.NOT_IN, program, type, + sarg.rangeSet.complement())); } else { final RangeSets.Consumer<C> consumer = new RangeToSql<>(operandSql, orList, v -> @@ -904,6 +901,24 @@ public abstract class SqlImplementor { return SqlUtil.createCall(SqlStdOperatorTable.OR, POS, orList); } + @SuppressWarnings("BetaApi") + private <C extends Comparable<C>> SqlNode toIn(SqlNode operandSql, + SqlBinaryOperator eqOp, SqlBinaryOperator inOp, + @Nullable RexProgram program, RelDataType type, RangeSet<C> rangeSet) { + final SqlNodeList list = rangeSet.asRanges().stream() + .map(range -> + toSql(program, + implementor().rexBuilder.makeLiteral(range.lowerEndpoint(), + type, true, true))) + .collect(SqlNode.toList()); + switch (list.size()) { + case 1: + return eqOp.createCall(POS, operandSql, list.get(0)); + default: + return inOp.createCall(POS, operandSql, list); + } + } + /** Converts an expression from {@link RexWindowBound} to {@link SqlNode} * format. * 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 ff5989f..2b93748 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -782,7 +782,6 @@ public class SqlDialect { public @Nullable SqlNode getCastSpec(RelDataType type) { int maxPrecision = -1; if (type instanceof AbstractSqlType) { - System.out.println("type.getSqlTypeName() = " + type.getSqlTypeName().getName()); switch (type.getSqlTypeName()) { case NULL: return null; 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 13ec1a3..d94d7c4 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 @@ -295,6 +295,36 @@ class RelToSqlConverterTest { sql(query).ok(expected); } + /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-4449";>[CALCITE-4449] + * Calcite generates incorrect SQL for Sarg 'x IS NULL OR x NOT IN + * (1, 2)'</a>. */ + @Test void testSelectWhereNotIn() { + final Function<RelBuilder, RelNode> relFn = b -> b + .scan("EMP") + .filter( + b.or(b.isNull(b.field("COMM")), + b.not(b.in(b.field("COMM"), b.literal(1), b.literal(2))))) + .build(); + final String expected = "SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"COMM\" IS NULL OR \"COMM\" NOT IN (1, 2)"; + relFn(relFn).ok(expected); + } + + @Test void testSelectWhereNotEquals() { + final Function<RelBuilder, RelNode> relFn = b -> b + .scan("EMP") + .filter( + b.or(b.isNull(b.field("COMM")), + b.not(b.in(b.field("COMM"), b.literal(1))))) + .build(); + final String expected = "SELECT *\n" + + "FROM \"scott\".\"EMP\"\n" + + "WHERE \"COMM\" IS NULL OR \"COMM\" <> 1"; + relFn(relFn).ok(expected); + } + @Test void testSelectQueryWithWhereClauseOfBasicOperators() { String query = "select * from \"product\" " + "where (\"product_id\" = 10 OR \"product_id\" <= 5) "
