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 ef1a83f659 [CALCITE-6638] Optimization that simplifies expressions
such as '1 > a or 1 <= a or a is null' to TRUE is incorrect when it requires
casts that are not lossless
ef1a83f659 is described below
commit ef1a83f659e8771c65c2541b92d2ef9cc2a05bea
Author: Xiong Duan <[email protected]>
AuthorDate: Sun Oct 20 22:26:24 2024 +0800
[CALCITE-6638] Optimization that simplifies expressions such as '1 > a or 1
<= a or a is null' to TRUE is incorrect when it requires casts that are not
lossless
---
.../apache/calcite/plan/RelOptPredicateList.java | 13 ++-
.../java/org/apache/calcite/rex/RexSimplify.java | 5 +
.../org/apache/calcite/rex/RexProgramTest.java | 127 +++++++++++++++++++++
core/src/test/resources/sql/conditions.iq | 29 +++++
4 files changed, 171 insertions(+), 3 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java
b/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java
index 749018531d..fa1236a31e 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptPredicateList.java
@@ -231,9 +231,16 @@ public class RelOptPredicateList {
return true;
}
for (RexNode p : pulledUpPredicates) {
- if (p.getKind() == SqlKind.IS_NOT_NULL
- && ((RexCall) p).getOperands().get(0).equals(e)) {
- return true;
+ if (p.getKind() == SqlKind.IS_NOT_NULL) {
+ // if e IS NOT NULL and e is TINYINT then cast(e as INTEGER) IS NOT
NULL
+ if (RexUtil.isLosslessCast(e)) {
+ if (isEffectivelyNotNull(((RexCall) e).getOperands().get(0))) {
+ return true;
+ }
+ }
+ if (((RexCall) p).getOperands().get(0).equals(e)) {
+ return true;
+ }
}
}
if (SqlKind.COMPARISON.contains(e.getKind())) {
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 e9002187b4..fddf8e5f25 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -2791,6 +2791,11 @@ public class RexSimplify {
}
@Override public boolean allowedInOr(RelOptPredicateList predicates) {
+ // if ref is not a 'loss-less' cast then can't be allowed to be used
+ // while simplifying other OR operands
+ if (ref.isA(SqlKind.CAST) && !RexUtil.isLosslessCast(ref)) {
+ return false;
+ }
return !ref.getType().isNullable()
|| predicates.isEffectivelyNotNull(ref);
}
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 4b062a7655..ce8a0abb49 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -1708,6 +1708,133 @@ class RexProgramTest extends RexProgramTestBase {
"true");
}
+ /** Unit test for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6638";>[CALCITE-6638]
+ * Optimization that simplifies expressions such as
+ * '1 > a or 1 <= a or a is null' to TRUE is incorrect
+ * when it requires casts that are not lossless</a>. */
+ @Test void testSimplifyOrTermsWithLosslessCasts() {
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType rowType = typeFactory.builder()
+ .add("a", intType).nullable(true)
+ .build();
+
+ final RelDataType intNullType =
typeFactory.createTypeWithNullability(intType, true);
+ final RexDynamicParam range = rexBuilder.makeDynamicParam(rowType, 0);
+ final RexNode aRef = rexBuilder.makeFieldAccess(range, 0);
+
+ // "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER), >=(1, CAST(?0.a):INTEGER))"
+ // when (?0.a) is INTEGER
+ // ==> "TRUE"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ lt(literal(1), rexBuilder.makeCast(intNullType, aRef)),
+ ge(literal(1), rexBuilder.makeCast(intNullType, aRef))),
+ "true");
+ }
+
+ @Test void testSimplifyOrTermsWithLosslessCasts1() {
+ 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);
+
+ // "IS NULL(?0.a), <(1, CAST(?0.a):INTEGER NOT NULL), >=(1,
CAST(?0.a):INTEGER NOT NULL)"
+ // when (?0.a) is INTEGER
+ // ==> "TRUE"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ lt(literal(1), rexBuilder.makeCast(intType, aRef)),
+ ge(literal(1), rexBuilder.makeCast(intType, aRef))),
+ "true");
+ }
+
+ @Test void testSimplifyOrTermsWithLosslessCasts2() {
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType bigIntType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
+ final RelDataType rowType = typeFactory.builder()
+ .add("a", bigIntType).nullable(true)
+ .build();
+
+ final RexDynamicParam range = rexBuilder.makeDynamicParam(rowType, 0);
+ final RexNode aRef = rexBuilder.makeFieldAccess(range, 0);
+
+ // "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER NOT NULL), >=(1,
CAST(?0.a):INTEGER NOT NULL))"
+ // when (?0.a) is BIGINT
+ // ==>
+ // "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER NOT NULL), >=(1,
CAST(?0.a):INTEGER NOT NULL))"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ lt(literal(1), rexBuilder.makeCast(intType, aRef)),
+ ge(literal(1), rexBuilder.makeCast(intType, aRef))),
+ "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER NOT NULL), >=(1,
CAST(?0.a):INTEGER NOT NULL))");
+ }
+
+ @Test void testSimplifyOrTermsWithLosslessCasts3() {
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType intNullType =
typeFactory.createTypeWithNullability(intType, true);
+ final RelDataType bigIntType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
+ final RelDataType rowType = typeFactory.builder()
+ .add("a", bigIntType).nullable(true)
+ .build();
+
+ final RexDynamicParam range = rexBuilder.makeDynamicParam(rowType, 0);
+ final RexNode aRef = rexBuilder.makeFieldAccess(range, 0);
+
+ // "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER), >=(1, CAST(?0.a):INTEGER))"
+ // when (?0.a) is BIGINT
+ // ==> "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER), >=(1,
CAST(?0.a):INTEGER))"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ lt(literal(1), rexBuilder.makeCast(intNullType, aRef)),
+ ge(literal(1), rexBuilder.makeCast(intNullType, aRef))),
+ "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER), >=(1,
CAST(?0.a):INTEGER))");
+ }
+
+ @Test void testSimplifyOrTermsWithLosslessCasts4() {
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType tinyIntType =
typeFactory.createSqlType(SqlTypeName.TINYINT);
+ final RelDataType rowType = typeFactory.builder()
+ .add("a", tinyIntType).nullable(true)
+ .build();
+
+ final RexDynamicParam range = rexBuilder.makeDynamicParam(rowType, 0);
+ final RexNode aRef = rexBuilder.makeFieldAccess(range, 0);
+
+ // "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER NOT NULL), >=(1,
CAST(?0.a):INTEGER NOT NULL))"
+ // when (?0.a) is TINYINT
+ // ==> "true"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ lt(literal(1), rexBuilder.makeCast(intType, aRef)),
+ ge(literal(1), rexBuilder.makeCast(intType, aRef))),
+ "true");
+ }
+
+ @Test void testSimplifyOrTermsWithLosslessCasts5() {
+ final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType intNullType =
typeFactory.createTypeWithNullability(intType, true);
+ final RelDataType tinyIntType =
typeFactory.createSqlType(SqlTypeName.TINYINT);
+ final RelDataType rowType = typeFactory.builder()
+ .add("a", tinyIntType).nullable(true)
+ .build();
+
+ final RexDynamicParam range = rexBuilder.makeDynamicParam(rowType, 0);
+ final RexNode aRef = rexBuilder.makeFieldAccess(range, 0);
+
+ // "OR(IS NULL(?0.a), <(1, CAST(?0.a):INTEGER), >=(1, CAST(?0.a):INTEGER))"
+ // when (?0.a) is TINYINT
+ // ==> "true"
+ checkSimplifyFilter(
+ or(isNull(aRef),
+ lt(literal(1), rexBuilder.makeCast(intNullType, aRef)),
+ ge(literal(1), rexBuilder.makeCast(intNullType, 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 0cec3ce4df..1acf5ecd4b 100644
--- a/core/src/test/resources/sql/conditions.iq
+++ b/core/src/test/resources/sql/conditions.iq
@@ -518,4 +518,33 @@ where 5 < deptno OR 5 >= deptno OR deptno IS NULL;
EnumerableTableScan(table=[[scott, EMP]])
!plan
+# Test case for [CALCITE-6638] Optimization that simplifies expressions such
as '1 > a or 1 <= a or a is null' to TRUE is incorrect when it requires casts
that are not lossless
+select *
+from "scott".emp
+where 5 < cast(deptno as integer) OR 5 >= cast(deptno as integer) 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
