You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2021/01/06 00:05:35 UTC
[calcite] branch master updated (1dd27f7 -> 8b9618f)
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a change to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git.
from 1dd27f7 [CALCITE-4434] Cannot implement 'CASE row WHEN row ...'
new 28d1c09 Cosmetic changes
new 8b9618f [CALCITE-4449] Generate nicer SQL for Sarg 'x IS NULL OR x NOT IN (1, 2)'
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 45 ++++++---
.../java/org/apache/calcite/sql/SqlDialect.java | 1 -
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 112 +++++++++++++--------
3 files changed, 102 insertions(+), 56 deletions(-)
[calcite] 02/02: [CALCITE-4449] Generate nicer SQL for Sarg 'x IS
NULL OR x NOT IN (1, 2)'
Posted by jh...@apache.org.
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 <jh...@apache.org>
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) "
[calcite] 01/02: Cosmetic changes
Posted by jh...@apache.org.
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 28d1c097e28dbc17f62ac1311755673d65337217
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Mon Dec 28 13:17:34 2020 -0800
Cosmetic changes
Code and comment formatting in RelToSqlConverterTest.
---
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 82 +++++++++++-----------
1 file changed, 42 insertions(+), 40 deletions(-)
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 4238c19..13ec1a3 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
@@ -707,7 +707,8 @@ class RelToSqlConverterTest {
* <a href="https://issues.apache.org/jira/browse/CALCITE-1174">[CALCITE-1174]
* When generating SQL, translate SUM0(x) to COALESCE(SUM(x), 0)</a>. */
@Test void testSum0BecomesCoalesce() {
- final Function<RelBuilder, RelNode> fn = b -> b.scan("EMP")
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
.aggregate(b.groupKey(),
b.aggregateCall(SqlStdOperatorTable.SUM0, b.field(3))
.as("s"))
@@ -716,7 +717,7 @@ class RelToSqlConverterTest {
+ "FROM `scott`.`EMP`";
final String expectedPostgresql = "SELECT COALESCE(SUM(\"MGR\"), 0) AS \"s\"\n"
+ "FROM \"scott\".\"EMP\"";
- relFn(fn)
+ relFn(relFn)
.withPostgresql()
.ok(expectedPostgresql)
.withMysql()
@@ -877,7 +878,8 @@ class RelToSqlConverterTest {
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2792">[CALCITE-2792]
- * Stackoverflow while evaluating filter with large number of OR conditions</a>. */
+ * StackOverflowError while evaluating filter with large number of OR
+ * conditions</a>. */
@Test void testBalancedBinaryCall() {
final Function<RelBuilder, RelNode> relFn = b -> b
.scan("EMP")
@@ -986,7 +988,7 @@ class RelToSqlConverterTest {
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-3207">[CALCITE-3207]
- * Fail to convert Join RelNode with like condition to sql statement </a>.
+ * Fail to convert Join RelNode with like condition to sql statement</a>.
*/
@Test void testJoinWithLikeConditionRel2Sql() {
final Function<RelBuilder, RelNode> relFn = b -> b
@@ -1517,10 +1519,10 @@ class RelToSqlConverterTest {
* replaces INs with ORs or sub-queries.
*/
@Test void testUnparseIn1() {
- final Function<RelBuilder, RelNode> relFn = b ->
- b.scan("EMP")
- .filter(b.in(b.field("DEPTNO"), b.literal(21)))
- .build();
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .filter(b.in(b.field("DEPTNO"), b.literal(21)))
+ .build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE \"DEPTNO\" = 21";
@@ -1539,15 +1541,15 @@ class RelToSqlConverterTest {
}
@Test void testUnparseInStruct1() {
- final Function<RelBuilder, RelNode> relFn = b ->
- b.scan("EMP")
- .filter(
- b.in(
- b.call(SqlStdOperatorTable.ROW,
- b.field("DEPTNO"), b.field("JOB")),
- b.call(SqlStdOperatorTable.ROW, b.literal(1),
- b.literal("PRESIDENT"))))
- .build();
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .filter(
+ b.in(
+ b.call(SqlStdOperatorTable.ROW,
+ b.field("DEPTNO"), b.field("JOB")),
+ b.call(SqlStdOperatorTable.ROW, b.literal(1),
+ b.literal("PRESIDENT"))))
+ .build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE ROW(\"DEPTNO\", \"JOB\") = ROW(1, 'PRESIDENT')";
@@ -1555,17 +1557,17 @@ class RelToSqlConverterTest {
}
@Test void testUnparseInStruct2() {
- final Function<RelBuilder, RelNode> relFn = b ->
- b.scan("EMP")
- .filter(
- b.in(
- b.call(SqlStdOperatorTable.ROW,
- b.field("DEPTNO"), b.field("JOB")),
- b.call(SqlStdOperatorTable.ROW, b.literal(1),
- b.literal("PRESIDENT")),
- b.call(SqlStdOperatorTable.ROW, b.literal(2),
- b.literal("PRESIDENT"))))
- .build();
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .filter(
+ b.in(
+ b.call(SqlStdOperatorTable.ROW,
+ b.field("DEPTNO"), b.field("JOB")),
+ b.call(SqlStdOperatorTable.ROW, b.literal(1),
+ b.literal("PRESIDENT")),
+ b.call(SqlStdOperatorTable.ROW, b.literal(2),
+ b.literal("PRESIDENT"))))
+ .build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "WHERE ROW(\"DEPTNO\", \"JOB\") IN (ROW(1, 'PRESIDENT'), ROW(2, 'PRESIDENT'))";
@@ -2501,18 +2503,18 @@ class RelToSqlConverterTest {
* <a href="https://issues.apache.org/jira/browse/CALCITE-4249">[CALCITE-4249]
* JDBC adapter cannot translate NOT LIKE in join condition</a>. */
@Test void testJoinOnNotLike() {
- final Function<RelBuilder, RelNode> relFn = b ->
- b.scan("EMP")
- .scan("DEPT")
- .join(JoinRelType.LEFT,
- b.and(
- b.equals(b.field(2, 0, "DEPTNO"),
- b.field(2, 1, "DEPTNO")),
- b.not(
- b.call(SqlStdOperatorTable.LIKE,
- b.field(2, 1, "DNAME"),
- b.literal("ACCOUNTING")))))
- .build();
+ final Function<RelBuilder, RelNode> relFn = b -> b
+ .scan("EMP")
+ .scan("DEPT")
+ .join(JoinRelType.LEFT,
+ b.and(
+ b.equals(b.field(2, 0, "DEPTNO"),
+ b.field(2, 1, "DEPTNO")),
+ b.not(
+ b.call(SqlStdOperatorTable.LIKE,
+ b.field(2, 1, "DNAME"),
+ b.literal("ACCOUNTING")))))
+ .build();
final String expectedSql = "SELECT *\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "LEFT JOIN \"scott\".\"DEPT\" "