This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 5a6d287 [CALCITE-1794] Expressions with numeric comparisons are not
simplified when CAST is present
5a6d287 is described below
commit 5a6d2874d2ea42fe6ef543bc1fd8ad6ba8b5fc36
Author: NobiGo <[email protected]>
AuthorDate: Mon Feb 21 16:18:36 2022 +0800
[CALCITE-1794] Expressions with numeric comparisons are not simplified when
CAST is present
---
.../java/org/apache/calcite/rex/RexSimplify.java | 2 ++
.../org/apache/calcite/rex/RexProgramTest.java | 31 ++++++++++++++++++++++
core/src/test/resources/sql/conditions.iq | 30 +++++++++++++++++++++
.../adapter/geode/rel/GeodeAllDataTypesTest.java | 2 +-
.../adapter/geode/rel/GeodeBookstoreTest.java | 5 ++--
5 files changed, 66 insertions(+), 4 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
index 8dd53a9..9019dc0 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -2806,6 +2806,7 @@ public class RexSimplify {
switch (left.getKind()) {
case INPUT_REF:
case FIELD_ACCESS:
+ case CAST:
switch (right.getKind()) {
case LITERAL:
return accept2b(left, kind, (RexLiteral) right, newTerms);
@@ -2817,6 +2818,7 @@ public class RexSimplify {
switch (right.getKind()) {
case INPUT_REF:
case FIELD_ACCESS:
+ case CAST:
return accept2b(right, kind.reverse(), (RexLiteral) left, newTerms);
default:
break;
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index 7f68dec..081d186 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -1726,6 +1726,37 @@ class RexProgramTest extends RexProgramTestBase {
"=(?0.notNullInt0, ?0.int1)");
}
+ @Test void testSimplifyEqualityAndNotEqualityWithOverlapping() {
+ final RexLiteral literal3 = literal(3);
+ final RexLiteral literal5 = literal(5);
+ final RexNode intExpr = vInt(0);
+ final RelDataType intType = literal3.getType();
+
+ // "AND(<>(?0.int0, 3), =(?0.int0, 5))" => "=(?0.int0, 5)"
+ checkSimplify(and(ne(intExpr, literal3), eq(intExpr, literal5)),
"=(?0.int0, 5)");
+ // "AND(=(?0.int0, 5), <>(?0.int0, 3))" => "=(?0.int0, 5)"
+ checkSimplify(and(eq(intExpr, literal5), ne(intExpr, literal3)),
"=(?0.int0, 5)");
+ // "AND(=(CAST(?0.int0):INTEGER NOT NULL, 5), <>(CAST(?0.int0):INTEGER NOT
NULL, 3))"
+ // =>
+ // "=(CAST(?0.int0):INTEGER NOT NULL, 5)"
+ checkSimplify(
+ and(ne(rexBuilder.makeCast(intType, intExpr, true), literal3),
+ eq(rexBuilder.makeCast(intType, intExpr, true), literal5)),
+ "=(CAST(?0.int0):INTEGER NOT NULL, 5)");
+ // "AND(<>(CAST(?0.int0):INTEGER NOT NULL, 3), =(CAST(?0.int0):INTEGER NOT
NULL, 5))"
+ // =>
+ // "=(CAST(?0.int0):INTEGER NOT NULL, 5)"
+ checkSimplify(
+ and(ne(rexBuilder.makeCast(intType, intExpr, true), literal3),
+ eq(rexBuilder.makeCast(intType, intExpr, true), literal5)),
+ "=(CAST(?0.int0):INTEGER NOT NULL, 5)");
+ // "AND(<>(CAST(?0.int0):INTEGER NOT NULL, 3), =(?0.int0, 5))"
+ // =>
+ // "AND(<>(CAST(?0.int0):INTEGER NOT NULL, 3), =(?0.int0, 5))"
+ checkSimplifyUnchanged(
+ and(ne(rexBuilder.makeCast(intType, intExpr, true), literal3),
eq(intExpr, literal5)));
+ }
+
@Test void testSimplifyAndIsNull() {
final RexNode aRef = input(tInt(true), 0);
final RexNode bRef = input(tInt(true), 1);
diff --git a/core/src/test/resources/sql/conditions.iq
b/core/src/test/resources/sql/conditions.iq
index e35b5f3..f83ea1e 100644
--- a/core/src/test/resources/sql/conditions.iq
+++ b/core/src/test/resources/sql/conditions.iq
@@ -327,4 +327,34 @@ where deptno > 5 AND deptno < 20 AND mgr IS NULL;
!ok
+# [CALCITE-1794] Expressions with numeric comparisons are not simplified when
CAST is present
+
+# Pull up predicate simplified plan has only 'deptno = 25' and has dropped the
'deptno <> 20' condition.
+select * from "scott".emp where deptno = 25 and deptno <> 20;
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[25],
expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# SARGs simplified plan has only 'deptno = 25' and has dropped the 'deptno <>
20' condition.
+select * from "scott".emp where deptno <> 20 and deptno = 25;
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[25],
expr#10=[=($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
# End conditions.iq
diff --git
a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
index 9df8550..edbce07 100644
---
a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
+++
b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeAllDataTypesTest.java
@@ -309,7 +309,7 @@ class GeodeAllDataTypesTest extends AbstractGeodeTest {
+ "stringValue IN SET('abc', 'def') OR floatValue = 1.5678 OR
dateValue "
+ "IN SET(DATE '2018-02-05', DATE '2018-02-06') OR timeValue "
+ "IN SET(TIME '03:22:23', TIME '07:22:23') OR timestampValue "
- + "IN SET(TIMESTAMP '2018-02-05 04:22:33', TIMESTAMP
'2017-02-05 04:22:33') "
+ + "IN SET(TIMESTAMP '2017-02-05 04:22:33', TIMESTAMP
'2018-02-05 04:22:33') "
+ "OR booleanValue = true OR booleanValue = false"));
}
}
diff --git
a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
index d34934f..d6efd09 100644
---
a/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
+++
b/geode/src/test/java/org/apache/calcite/adapter/geode/rel/GeodeBookstoreTest.java
@@ -116,8 +116,7 @@ class GeodeBookstoreTest extends AbstractGeodeTest {
.returnsUnordered("author=Jim Heavisides", "author=Daisy Mae West")
.explainContains("PLAN=GeodeToEnumerableConverter\n"
+ " GeodeProject(author=[$4])\n"
- + " GeodeFilter(condition=[OR(=(CAST($0):INTEGER, 123), "
- + "=(CAST($0):INTEGER, 789))])\n"
+ + " GeodeFilter(condition=[SEARCH(CAST($0):INTEGER, Sarg[123,
789])])\n"
+ " GeodeTableScan(table=[[geode, BookMaster]])\n")
.queryContains(
GeodeAssertions.query(expectedQuery));
@@ -455,7 +454,7 @@ class GeodeBookstoreTest extends AbstractGeodeTest {
@Test void testSqlDisjunction() {
String expectedQuery = "SELECT author AS author FROM /BookMaster "
- + "WHERE itemNumber IN SET(789, 123)";
+ + "WHERE itemNumber IN SET(123, 789)";
calciteAssert().query("SELECT author FROM geode.BookMaster "
+ "WHERE itemNumber = 789 OR itemNumber = 123").runs()
