This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new b8ffcce8ec [CALCITE-5780] Simplify '1 > x OR 1 <= x OR x IS NULL' to
TRUE
b8ffcce8ec is described below
commit b8ffcce8ecbfe7222c814b887c4c2dc1647853f3
Author: Runkang He <[email protected]>
AuthorDate: Thu Jun 15 08:34:58 2023 +0800
[CALCITE-5780] Simplify '1 > x OR 1 <= x OR x IS NULL' to TRUE
---
.../java/org/apache/calcite/rex/RexSimplify.java | 11 ++--
.../org/apache/calcite/rex/RexProgramTest.java | 64 +++++++++++++++++++++-
core/src/test/resources/sql/conditions.iq | 28 ++++++++++
3 files changed, 96 insertions(+), 7 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 9068726a49..ef10a271e5 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -1887,11 +1887,10 @@ public class RexSimplify {
case GREATER_THAN:
case GREATER_THAN_OR_EQUAL:
final RexCall call = (RexCall) predicate;
- if (call.operands.get(0).equals(ref)
- && call.operands.get(1) instanceof RexLiteral) {
- final RexLiteral literal = (RexLiteral) call.operands.get(1);
- final C c1 = literal.getValueAs(clazz);
- assert c1 != null : "value must not be null in " + literal;
+ final Comparison comparison = Comparison.of(call);
+ if (comparison != null && comparison.ref.equals(ref)) {
+ final C c1 = comparison.literal.getValueAs(clazz);
+ assert c1 != null : "value must not be null in " +
comparison.literal;
switch (predicate.getKind()) {
case NOT_EQUALS:
// We want to intersect result with the range set of everything but
@@ -1906,7 +1905,7 @@ public class RexSimplify {
result = RangeSets.minus(result, pointRange);
break;
default:
- final Range<C> r1 = range(predicate.getKind(), c1);
+ final Range<C> r1 = range(comparison.kind, c1);
if (result.encloses(r1)) {
// Given these predicates, term is always satisfied.
// e.g. r0 is "$0 < 10", r1 is "$0 < 5"
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 e2f7562462..9af7b8e299 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -1145,7 +1145,7 @@ class RexProgramTest extends RexProgramTestBase {
checkSimplifyFilter(
and(gt(literal(1), aRef), gt(literal(5), aRef)),
RelOptPredicateList.EMPTY,
- "<(?0.a, 1)");
+ ">(1, ?0.a)");
// condition "1 > a && a > 5" yields false
checkSimplifyFilter(
@@ -1604,6 +1604,68 @@ class RexProgramTest extends RexProgramTestBase {
"true");
}
+ /** Unit test for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5780";>[CALCITE-5780]
+ * Simplify '1 > x OR 1 <= x OR x IS NULL' to TRUE</a>. */
+ @Test void testSimplifyOrTermsWithReverseOrderComparison() {
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType rowType = typeFactory.builder()
+ .add("a", intType).nullable(true)
+ .build();
+
+ final RexDynamicParam range = rexBuilder.makeDynamicParam(rowType, 0);
+ final RexNode aRef = rexBuilder.makeFieldAccess(range, 0);
+
+ // "1 > a or 1 <= a or a is null" ==> "true"
+ checkSimplifyFilter(
+ or(gt(literal(1), aRef),
+ le(literal(1), aRef),
+ isNull(aRef)),
+ "true");
+
+ // "1 <= a or 1 > a or a is null" ==> "true"
+ checkSimplifyFilter(
+ or(le(literal(1), aRef),
+ gt(literal(1), aRef),
+ isNull(aRef)),
+ "true");
+
+ // "a is null or 1 > a or 1 <= a" ==> "true"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ gt(literal(1), aRef),
+ le(literal(1), aRef)),
+ "true");
+
+ // "2 > a or 0 < a or a is null" ==> "true"
+ checkSimplifyFilter(
+ or(gt(literal(2), aRef),
+ lt(literal(0), aRef),
+ isNull(aRef)),
+ "true");
+
+ // "1 > a or a >= 1 or a is null" ==> "true"
+ checkSimplifyFilter(
+ or(gt(literal(1), aRef),
+ ge(aRef, literal(1)),
+ isNull(aRef)),
+ "true");
+
+ // "1 <= a or a < 1 or a is null" ==> "true"
+ checkSimplifyFilter(
+ or(le(literal(1), aRef),
+ lt(aRef, literal(1)),
+ isNull(aRef)),
+ "true");
+
+ // "a >= 1 or 1 > a or a is null" ==> "true"
+ checkSimplifyFilter(
+ or(ge(aRef, literal(1)),
+ gt(literal(1), aRef),
+ isNull(aRef)),
+ "true");
+ }
+
@Test void testSimplifyRange() {
final RexNode aRef = input(tInt(), 0);
// ((0 < a and a <= 10) or a >= 15) and a <> 6 and a <> 12
diff --git a/core/src/test/resources/sql/conditions.iq
b/core/src/test/resources/sql/conditions.iq
index 1ef97e206a..a5bf3d29d2 100644
--- a/core/src/test/resources/sql/conditions.iq
+++ b/core/src/test/resources/sql/conditions.iq
@@ -490,5 +490,33 @@ where emp.empno = 7902;
!ok
+# Test case for [CALCITE-5780] Simplify '1 > x OR 1 <= x OR x IS NULL' to TRUE
+select *
+from "scott".emp
+where 5 < deptno OR 5 >= deptno OR deptno IS NULL;
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(14 rows)
+
+!ok
+
+EnumerableTableScan(table=[[scott, EMP]])
+!plan
# End conditions.iq
