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 6ee860d4c9 [CALCITE-7032] Simplify 'NULL > ALL (ARRAY[1,2,NULL])' to
'NULL'
6ee860d4c9 is described below
commit 6ee860d4c95f025d1b34acad3564d3eee1833591
Author: Xiong Duan <[email protected]>
AuthorDate: Thu May 22 18:32:58 2025 +0800
[CALCITE-7032] Simplify 'NULL > ALL (ARRAY[1,2,NULL])' to 'NULL'
---
.../main/java/org/apache/calcite/plan/Strong.java | 15 ++++
.../java/org/apache/calcite/rex/RexAnalyzer.java | 1 +
.../java/org/apache/calcite/rex/RexSimplify.java | 38 ++++++++--
.../java/org/apache/calcite/sql/SqlOperator.java | 21 ++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 8 +--
.../org/apache/calcite/rex/RexProgramTest.java | 83 +++++++++++++++++++++-
core/src/test/resources/sql/sub-query.iq | 29 +++++++-
7 files changed, 180 insertions(+), 15 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/plan/Strong.java
b/core/src/main/java/org/apache/calcite/plan/Strong.java
index fa8fc6db61..b92c98f560 100644
--- a/core/src/main/java/org/apache/calcite/plan/Strong.java
+++ b/core/src/main/java/org/apache/calcite/plan/Strong.java
@@ -21,11 +21,13 @@
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexUnknownAs;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlOperator;
+import org.apache.calcite.sql.fun.SqlQuantifyOperator;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Sarg;
@@ -250,6 +252,19 @@ public boolean isNull(RexNode node) {
return sarg.nullAs == RexUnknownAs.UNKNOWN;
}
return false;
+ case SOME:
+ case ALL:
+ final RexCall rexCall = (RexCall) node;
+ // For example:
+ // select NULL > all (select comm from emp where 1 = 0) from emp
+ // return FALSE when the sub-query returns 0 row
+ if (rexCall instanceof RexSubQuery) {
+ return false;
+ }
+ if (rexCall.getOperator() instanceof SqlQuantifyOperator) {
+ return anyNull(rexCall.getOperands());
+ }
+ return false;
default:
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexAnalyzer.java
b/core/src/main/java/org/apache/calcite/rex/RexAnalyzer.java
index 5da2b64375..5dcad7aa9e 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexAnalyzer.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexAnalyzer.java
@@ -137,6 +137,7 @@ private static class VariableCollector extends
RexVisitorImpl<Void> {
case M2V:
case OTHER_FUNCTION:
case V2M:
+ case ARRAY_VALUE_CONSTRUCTOR:
++unsupportedCount;
return null;
default:
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 ee20c5ad5d..8739274f8e 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -263,7 +263,7 @@ public RexNode simplifyUnknownAs(RexNode e, RexUnknownAs
unknownAs) {
* Verify adds an overhead that is only acceptable for a top-level call.
*/
RexNode simplify(RexNode e, RexUnknownAs unknownAs) {
- if (STRONG.isNull(e)) {
+ if (isSafeExpression(e) && STRONG.isNull(e)) {
// Only boolean NULL (aka UNKNOWN) can be converted to FALSE. Even in
// unknownAs=FALSE mode, we must not convert a NULL integer (say) to
FALSE
if (e.getType().getSqlTypeName() == SqlTypeName.BOOLEAN) {
@@ -1330,11 +1330,17 @@ enum SafeRexVisitor implements RexVisitor<Boolean> {
@SuppressWarnings("ImmutableEnumChecker")
private final Set<SqlKind> safeOps;
+ @SuppressWarnings("ImmutableEnumChecker")
+ private final ImmutableSet<SqlOperator> safeOperators;
SafeRexVisitor() {
- Set<SqlKind> safeOps = EnumSet.noneOf(SqlKind.class);
+ ImmutableSet.Builder<SqlOperator> builder = ImmutableSet.builder();
+ builder.addAll(SqlStdOperatorTable.QUANTIFY_OPERATORS);
+ safeOperators = builder.build();
+ Set<SqlKind> safeOps = EnumSet.noneOf(SqlKind.class);
safeOps.addAll(SqlKind.COMPARISON);
+ safeOps.add(SqlKind.ARRAY_VALUE_CONSTRUCTOR);
safeOps.add(SqlKind.PLUS_PREFIX);
safeOps.add(SqlKind.MINUS_PREFIX);
safeOps.add(SqlKind.CHECKED_MINUS_PREFIX);
@@ -1385,10 +1391,34 @@ enum SafeRexVisitor implements RexVisitor<Boolean> {
}
@Override public Boolean visitCall(RexCall call) {
- if (!safeOps.contains(call.getKind())) {
+ SqlKind sqlKind = call.getKind();
+ SqlOperator sqlOperator = call.getOperator();
+
+ switch (sqlKind) {
+ case DIVIDE:
+ case MOD:
+ List<RexNode> operands = call.getOperands();
+ boolean isSafe = RexVisitorImpl.visitArrayAnd(this,
ImmutableList.of(operands.get(0)));
+ if (!isSafe) {
+ return false;
+ }
+ if (operands.get(1) instanceof RexLiteral) {
+ RexLiteral literal = (RexLiteral) operands.get(1);
+ return RexUtil.isNullLiteral(literal, true);
+ }
return false;
+ default:
+ break;
+ }
+
+ if (sqlOperator.isSafeOperator()
+ || RexUtil.isLosslessCast(call)
+ || safeOps.contains(sqlKind)
+ || safeOperators.contains(sqlOperator)) {
+ return RexVisitorImpl.visitArrayAnd(this, call.operands);
}
- return RexVisitorImpl.visitArrayAnd(this, call.operands);
+
+ return false;
}
@Override public Boolean visitOver(RexOver over) {
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
index 318318bf42..8d0755a21f 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlOperator.java
@@ -1036,6 +1036,27 @@ public <R> void acceptCall(
return null;
}
+ /**
+ * Returns whether this is a safe operator.
+ *
+ * <p>If an operator is safe, then it never causes a run-time exception.
+ *
+ * <p>For example, the {@code a/b} is not safe. Because the
+ * {@code SqlStdOperatorTable.DIVIDE} may throw the exception when dividing
by zero.
+ *
+ * <p>By default, returns {@code false}, which means the operator is not
safe.
+ *
+ * <p>If an operator is safe, then some optimizations can be performed in
+ * {@code org.apache.calcite.rex.RexSimplify}.
+ *
+ * <p>For example:
+ * {@code NULL + a} can be optimized to {@code NULL}.
+ *
+ */
+ public Boolean isSafeOperator() {
+ return false;
+ }
+
/**
* Returns whether this operator is monotonic.
*
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index e431129723..1b8b9d5cdb 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -4720,12 +4720,10 @@ private SqlDialect nonOrdinalDialect() {
+ "FROM dept AS d LEFT JOIN emp AS e\n"
+ " ON CASE WHEN e.job = 'PRESIDENT' THEN true ELSE d.deptno = 10
END\n"
+ "WHERE e.job LIKE 'PRESIDENT'";
- final String expected = "SELECT \"DEPT\".\"DEPTNO\","
- + " \"EMP\".\"DEPTNO\" AS \"DEPTNO0\"\n"
+ final String expected = "SELECT \"DEPT\".\"DEPTNO\", \"EMP\".\"DEPTNO\" AS
\"DEPTNO0\"\n"
+ "FROM \"SCOTT\".\"DEPT\"\n"
- + "LEFT JOIN \"SCOTT\".\"EMP\""
- + " ON CASE WHEN \"EMP\".\"JOB\" = 'PRESIDENT' THEN TRUE"
- + " ELSE CAST(\"DEPT\".\"DEPTNO\" AS INTEGER) = 10 END\n"
+ + "LEFT JOIN \"SCOTT\".\"EMP\" ON \"EMP\".\"JOB\" = 'PRESIDENT' OR "
+ + "CAST(\"DEPT\".\"DEPTNO\" AS INTEGER) = 10 AND \"EMP\".\"JOB\" =
'PRESIDENT' IS NOT TRUE\n"
+ "WHERE \"EMP\".\"JOB\" LIKE 'PRESIDENT'";
sql(sql)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
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 1224c63298..ea44d22e9b 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -1835,6 +1835,45 @@ private void checkExponentialCnf(int n) {
"true");
}
+ /** Unit test for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7032";>[CALCITE-7032]
+ * Simplify 'NULL > ALL (ARRAY[1,2,NULL])' to 'NULL'</a>. */
+ @Test void testSimplifyQuantifyOperatorsWithArray() {
+ RexNode operand1 = nullInt;
+ RelDataType arrayType = tArray(tInt(true));
+ RelDataType arrayType2 = tArray(tSmallInt(true));
+ RexNode operand2 =
+ rexBuilder.makeCall(arrayType,
SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
+ ImmutableList.of(literal(1), literal(2), nullInt));
+ // "NULL > SOME (ARRAY[1,2,NULL])"
+ // ==> "NULL"
+ checkSimplify3(rexBuilder.makeCall(SqlStdOperatorTable.SOME_GT, operand1,
operand2),
+ "null:BOOLEAN", "false", "true");
+
+ // "NULL > SOME (ARRAY[CAST(10 AS SMALLINT),2,NULL])"
+ // ==> "NULL"
+ operand2 =
+ rexBuilder.makeCall(arrayType,
SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
+ ImmutableList.of(cast(literal(10), tSmallInt()), literal(2),
nullInt));
+ checkSimplify3(rexBuilder.makeCall(SqlStdOperatorTable.SOME_GT, operand1,
operand2),
+ "null:BOOLEAN", "false", "true");
+
+ // "NULL > SOME (ARRAY[CAST(100000 AS SMALLINT),2,NULL])"
+ // ==> "NULL > SOME (ARRAY[CAST(100000 AS SMALLINT),2,NULL])"
+ operand2 =
+ rexBuilder.makeCall(arrayType,
SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
+ ImmutableList.of(cast(literal(100000), tSmallInt()), literal(2),
nullInt));
+ checkSimplifyUnchanged(rexBuilder.makeCall(SqlStdOperatorTable.SOME_GT,
operand1, operand2));
+
+ // "NULL > SOME (CAST(ARRAY[100000,2,NULL]) AS SMALLINT ARRAY)"
+ // ==> "NULL > SOME (CAST(ARRAY[100000,2,NULL]) AS SMALLINT ARRAY)"
+ operand2 =
+ cast(
+ rexBuilder.makeCall(arrayType,
SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
+ ImmutableList.of(literal(100000), literal(2), nullInt)),
arrayType2);
+ checkSimplifyUnchanged(rexBuilder.makeCall(SqlStdOperatorTable.SOME_GT,
operand1, operand2));
+ }
+
@Test void testSimplifyRange() {
final RexNode aRef = input(tInt(), 0);
// ((0 < a and a <= 10) or a >= 15) and a <> 6 and a <> 12
@@ -2625,6 +2664,38 @@ trueLiteral, literal(1),
checkSimplify(caseNode, "<=(?0.notNullInt0, 1)");
}
+ /** Unit test for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7032";>[CALCITE-7032]
+ * Simplify 'NULL > ALL (ARRAY[1,2,NULL])' to 'NULL'</a>. */
+ @Test void testSimplifyDivideSafe() {
+ // null + (a/0)/4
+ // ==>
+ // null + (a/0)/4
+ simplify = simplify.withParanoid(false);
+ RexNode divideNode0 = plus(nullInt, div(div(vIntNotNull(), literal(0)),
literal(4)));
+ checkSimplifyUnchanged(divideNode0);
+ // null + a/4
+ // ==>
+ // null + a/4
+ RexNode divideNode1 = plus(nullInt, div(vIntNotNull(), literal(4)));
+ checkSimplifyUnchanged(divideNode1);
+ // null + a/null
+ // ==>
+ // null
+ RexNode divideNode2 = plus(nullInt, div(vIntNotNull(), nullInt));
+ checkSimplify(divideNode2, "null:INTEGER");
+ // null + null/0
+ // ==>
+ // null + null/0
+ RexNode divideNode3 = plus(nullInt, div(vIntNotNull(), literal(0)));
+ checkSimplifyUnchanged(divideNode3);
+ // null + a/b
+ // ==>
+ // null + a/b
+ RexNode divideNode4 = plus(nullInt, div(vIntNotNull(), vIntNotNull()));
+ checkSimplifyUnchanged(divideNode4);
+ }
+
@Test void testPushNotIntoCase() {
checkSimplify(
not(
@@ -4000,8 +4071,7 @@ private SqlSpecialOperatorWithPolicy(String name, SqlKind
kind, int prec, boolea
@Test void testSimplifyFunctionWithStrongPolicy() {
final SqlOperator op =
new SqlSpecialOperator("OP1", SqlKind.OTHER_FUNCTION, 0, false,
- ReturnTypes.BOOLEAN, null, null) {
- };
+ ReturnTypes.BOOLEAN, null, null);
// Operator with no Strong.Policy defined: no simplification can be made
checkSimplifyUnchanged(rexBuilder.makeCall(op, vInt()));
checkSimplifyUnchanged(rexBuilder.makeCall(op, vIntNotNull()));
@@ -4011,7 +4081,7 @@ private SqlSpecialOperatorWithPolicy(String name, SqlKind
kind, int prec, boolea
new SqlSpecialOperatorWithPolicy("OP2", SqlKind.OTHER_FUNCTION, 0,
false, ReturnTypes.BOOLEAN, null, null, Strong.Policy.AS_IS) {
};
- // Operator with Strong.Policy.AS_IS: no simplification can be made
+ // Operator with Strong.Policy.AS_IS but not safe: no simplification can
be made
checkSimplifyUnchanged(rexBuilder.makeCall(opPolicyAsIs, vInt()));
checkSimplifyUnchanged(rexBuilder.makeCall(opPolicyAsIs, vIntNotNull()));
checkSimplifyUnchanged(rexBuilder.makeCall(opPolicyAsIs, nullInt));
@@ -4019,12 +4089,19 @@ private SqlSpecialOperatorWithPolicy(String name,
SqlKind kind, int prec, boolea
final SqlOperator opPolicyAny =
new SqlSpecialOperatorWithPolicy("OP3", SqlKind.OTHER_FUNCTION, 0,
false, ReturnTypes.BOOLEAN, null, null, Strong.Policy.ANY) {
+ @Override public Boolean isSafeOperator() {
+ return true;
+ }
};
// Operator with Strong.Policy.ANY: simplification possible with null
parameter
checkSimplifyUnchanged(rexBuilder.makeCall(opPolicyAny, vInt()));
checkSimplifyUnchanged(rexBuilder.makeCall(opPolicyAny, vIntNotNull()));
checkSimplify3(rexBuilder.makeCall(opPolicyAny, nullInt),
"null:BOOLEAN", "false", "true");
+ // Operator with not safe operand: no simplification can be made
+ checkSimplifyUnchanged(
+ rexBuilder.makeCall(opPolicyAny,
+ rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE, vIntNotNull(),
vIntNotNull())));
}
@Test void testSimplifyVarbinary() {
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 0646f2c3b5..9feb458e53 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -3651,7 +3651,7 @@ select * from "scott".emp where comm in (300, 500, null);
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:DECIMAL(12, 2)], expr#12=[=($t8,
$t11)], expr#13=[OR($t10, $t12)], proj#0..7=[{exprs}], $condition=[$t13])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3679,7 +3679,7 @@ select *, comm in (300, 500, null) as i from "scott".emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[OR($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:DECIMAL(12, 2)], expr#12=[=($t8,
$t11)], expr#13=[OR($t10, $t12)], proj#0..7=[{exprs}], I=[$t13])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3720,7 +3720,7 @@ select *, comm not in (300, 500, null) as i from
"scott".emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(12, 2)), (300.00:DECIMAL(12,
2)..500.00:DECIMAL(12, 2)), (500.00:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[AND($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(12, 2)), (300.00:DECIMAL(12,
2)..500.00:DECIMAL(12, 2)), (500.00:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:DECIMAL(12, 2)], expr#12=[<>($t8,
$t11)], expr#13=[AND($t10, $t12)], proj#0..7=[{exprs}], I=[$t13])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -4863,6 +4863,29 @@ EnumerableCalc(expr#0=[{inputs}], expr#1=[false],
expr#2=[CAST($t1):BOOLEAN], DE
EnumerableValues(tuples=[[{ 10 }, { 10 }, { 20 }, { 30 }, { 30 }, { 50 }, {
50 }, { 60 }, { null }]])
!plan
+# Same as previous; but LHS is NULL
+select deptno, null > some(select deptno from dept where false) from emp;
++--------+--------+
+| DEPTNO | EXPR$1 |
++--------+--------+
+| 10 | false |
+| 20 | false |
+| 30 | false |
+| 10 | false |
+| 30 | false |
+| 50 | false |
+| 50 | false |
+| 60 | false |
+| | false |
++--------+--------+
+(9 rows)
+
+!ok
+
+EnumerableCalc(expr#0=[{inputs}], expr#1=[false], expr#2=[CAST($t1):BOOLEAN],
DEPTNO=[$t0], EXPR$1=[$t2])
+ EnumerableValues(tuples=[[{ 10 }, { 10 }, { 20 }, { 30 }, { 30 }, { 50 }, {
50 }, { 60 }, { null }]])
+!plan
+
# Test case about ANY sub-query when sub-query return 0 row
select * from emp where deptno > any(select deptno from dept where false);
+-------+--------+--------+
