This is an automated email from the ASF dual-hosted git repository. liyafan pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 8b62479e55e8afc2ef56762a97e5ace91da4cc5f 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()
