You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by xi...@apache.org on 2023/07/10 01:25:09 UTC
[calcite] branch main updated: [CALCITE-5769] Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
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 2c007fb087 [CALCITE-5769] Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
2c007fb087 is described below
commit 2c007fb0878005848c81c81f171a1bf6b669896a
Author: nobigo <no...@gmail.com>
AuthorDate: Tue Jul 4 19:34:57 2023 +0800
[CALCITE-5769] Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
---
.../java/org/apache/calcite/rex/RexSimplify.java | 12 +++
.../org/apache/calcite/rex/RexProgramTest.java | 73 +++++++++++++
core/src/test/resources/sql/misc.iq | 120 +++++++++++++++++++++
3 files changed, 205 insertions(+)
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 ef10a271e5..d3c1f93aa5 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -973,6 +973,12 @@ public class RexSimplify {
if (!a.getType().isNullable() && isSafeExpression(a)) {
return rexBuilder.makeLiteral(true);
}
+ if (RexUtil.isLosslessCast(a)) {
+ if (!a.getType().isNullable()) {
+ return rexBuilder.makeLiteral(true);
+ }
+ return rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, RexUtil.removeCast(a));
+ }
if (predicates.pulledUpPredicates.contains(a)) {
return rexBuilder.makeLiteral(true);
}
@@ -1022,6 +1028,12 @@ public class RexSimplify {
if (!a.getType().isNullable() && isSafeExpression(a)) {
return rexBuilder.makeLiteral(false);
}
+ if (RexUtil.isLosslessCast(a)) {
+ if (!a.getType().isNullable()) {
+ return rexBuilder.makeLiteral(false);
+ }
+ return rexBuilder.makeCall(SqlStdOperatorTable.IS_NULL, RexUtil.removeCast(a));
+ }
if (RexUtil.isNull(a)) {
return rexBuilder.makeLiteral(true);
}
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 9af7b8e299..068a96de34 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -2520,6 +2520,79 @@ class RexProgramTest extends RexProgramTestBase {
checkSimplifyUnchanged(isNull(cast(vVarcharNotNull(), tInt(false))));
}
+ /** Unit test for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5769">[CALCITE-5769]
+ * Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'</a>. */
+ @Test void testSimplifyCastIsNull3() {
+ // "(cast A as bigint) IS NULL" when A is int and A is not null
+ // ==>
+ // "false"
+ checkSimplify(isNull(cast(vIntNotNull(), tBigInt(false))), "false");
+ // "(cast A as smallint) IS NULL" when A is int and A is not null
+ // ==>
+ // "(cast A as smallint) IS NULL"
+ checkSimplifyUnchanged(isNull(cast(vIntNotNull(), tSmallInt(false))));
+ // "(cast A as varbinary) IS NULL" when A is varchar and A is not null
+ // ==>
+ // "(cast A as varbinary) IS NULL"
+ checkSimplifyUnchanged(isNotNull(cast(vVarcharNotNull(), tVarbinary(false))));
+ }
+
+ /** Unit test for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5769">[CALCITE-5769]
+ * Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'</a>. */
+ @Test void testSimplifyCastIsNull4() {
+ // "(cast A as bigint) IS NULL" when A is int and A is nullable
+ // ==>
+ // "A IS NULL"
+ checkSimplify(isNull(cast(vInt(), tBigInt(true))), "IS NULL(?0.int0)");
+ // "(cast A as smallint) IS NULL" when A is int and A is nullable
+ // ==>
+ // "(cast A as smallint) IS NULL"
+ checkSimplifyUnchanged(isNull(cast(vInt(), tSmallInt(true))));
+ // "(cast A as varbinary) IS NULL" when A is varchar and A is nullable
+ // ==>
+ // "(cast A as varbinary) IS NULL"
+ checkSimplifyUnchanged(isNotNull(cast(vVarchar(), tVarbinary(true))));
+ }
+
+ /** Unit test for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5769">[CALCITE-5769]
+ * Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'</a>. */
+ @Test void testSimplifyCastIsNotNull() {
+ // "(cast A as bigint) IS NOT NULL" when A is int and A is not null
+ // ==>
+ // "true"
+ checkSimplify(isNotNull(cast(vIntNotNull(), tBigInt(false))), "true");
+ // "(cast A as smallint) IS NOT NULL" when A is int and A is not null
+ // ==>
+ // "(cast A as smallint) IS NOT NULL"
+ checkSimplifyUnchanged(isNotNull(cast(vIntNotNull(), tSmallInt(false))));
+ // "(cast A as varbinary) IS NOT NULL" when A is varchar and A is not null
+ // ==>
+ // "(cast A as varbinary) IS NOT NULL"
+ checkSimplifyUnchanged(isNotNull(cast(vVarcharNotNull(), tVarbinary(false))));
+ }
+
+ /** Unit test for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-5769">[CALCITE-5769]
+ * Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'</a>. */
+ @Test void testSimplifyCastIsNotNull2() {
+ // "(cast A as bigint) IS NOT NULL" when A is int and A is nullable
+ // ==>
+ // "A IS NOT NULL"
+ checkSimplify(isNotNull(cast(vInt(), tBigInt(true))), "IS NOT NULL(?0.int0)");
+ // "(cast A as smallint) IS NOT NULL" when A is int and A is nullable
+ // ==>
+ // "(cast A as smallint) IS NOT NULL"
+ checkSimplifyUnchanged(isNotNull(cast(vInt(), tSmallInt(true))));
+ // "(cast A as varbinary) IS NOT NULL" when A is varchar and A is nullable
+ // ==>
+ // "(cast A as varbinary) IS NOT NULL"
+ checkSimplifyUnchanged(isNotNull(cast(vVarchar(), tVarbinary(true))));
+ }
+
+
@Test void checkSimplifyDynamicParam() {
checkSimplify(isNotNull(lt(vInt(0), vInt(1))),
"AND(IS NOT NULL(?0.int0), IS NOT NULL(?0.int1))");
diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq
index c4419ee456..b868a090f1 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2400,4 +2400,124 @@ TS
1969-07-21 00:00:00
!ok
+!use scott
+!set outputformat mysql
+
+# [CALCITE-5769] Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
+
+# Test CAST(e as t) IS NOT NULL when CAST is lossless And e IS NOT NULL
+select cast(deptno as integer) IS NOT NULL from "scott".dept;
++--------+
+| EXPR$0 |
++--------+
+| true |
+| true |
+| true |
+| true |
++--------+
+(4 rows)
+
+!ok
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], EXPR$0=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test CAST(e as t) IS NULL when CAST is lossless And e IS NOT NULL
+select cast(deptno as integer) IS NULL from "scott".dept;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| false |
+| false |
+| false |
++--------+
+(4 rows)
+
+!ok
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], EXPR$0=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test CAST(e as t) IS NOT NULL when CAST is lossless And e is nullable
+select cast(mgr as integer) IS NOT NULL from "scott".emp;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
++--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)], EXPR$0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test CAST(e as t) IS NULL when CAST is lossless And e is nullable
+select cast(mgr as integer) IS NULL from "scott".emp;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| true |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
++--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t3)], EXPR$0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test CAST(e as t) IS NOT NULL when CAST is not lossless And e is nullable
+select cast(comm as integer) IS NOT NULL from "scott".emp;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| true |
+| true |
+| true |
+| true |
++--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):INTEGER], expr#9=[IS NOT NULL($t8)], EXPR$0=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
# End misc.iq