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 12e7d621bb [CALCITE-7044] Add internal operator CAST NOT NULL to
enhance rewrite COALESCE operator
12e7d621bb is described below
commit 12e7d621bbb19aa32cb45329cb84532115037b7a
Author: Xiong Duan <[email protected]>
AuthorDate: Wed May 28 17:20:14 2025 +0800
[CALCITE-7044] Add internal operator CAST NOT NULL to enhance rewrite
COALESCE operator
---
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 ++
.../calcite/sql/fun/SqlCoalesceFunction.java | 2 +-
.../calcite/sql/fun/SqlInternalOperators.java | 34 ++++++++++++++++++++++
.../calcite/sql2rel/StandardConvertletTable.java | 8 +++++
.../apache/calcite/test/SqlToRelConverterTest.java | 12 ++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 2 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 25 +++++++++++++++-
core/src/test/resources/sql/sub-query.iq | 4 +--
8 files changed, 85 insertions(+), 5 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 1063b9df90..d2c57864b8 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -662,6 +662,9 @@ public enum SqlKind {
/** {@code IS NOT NULL} operator. */
IS_NOT_NULL,
+ /** {@code CAST NOT NULL} operator. */
+ CAST_NOT_NULL,
+
/** {@code PRECEDING} qualifier of an interval end-point in a window
* specification. */
PRECEDING,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
index cd3c46040c..b4af37b32d 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCoalesceFunction.java
@@ -87,7 +87,7 @@ public SqlCoalesceFunction() {
for (SqlNode operand : Util.skipLast(operands)) {
whenList.add(
SqlStdOperatorTable.IS_NOT_NULL.createCall(pos, operand));
- thenList.add(SqlNode.clone(operand));
+ thenList.add(SqlInternalOperators.CAST_NOT_NULL.createCall(pos,
SqlNode.clone(operand)));
}
SqlNode elseExpr = Util.last(operands);
assert call.getFunctionQuantifier() == null;
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
index 139f91068b..dd1098fe4f 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
@@ -224,6 +224,40 @@ private SqlInternalOperators() {
public static final SqlAggFunction LITERAL_AGG =
SqlLiteralAggFunction.INSTANCE;
+ /** CAST NOT NULL operator used for cast expression to make it non-nullable
in SqlNode.
+ *
+ * <p>For example:
+ * <pre>{@code COALESCE(a,b)
+ * }</pre>
+ *
+ * <p>is converted to
+ *
+ * <pre>{@code CASE WHEN a is not null THEN a ELSE b
+ * }</pre>
+ * by {@code SqlCoalesceFunction#rewriteCall}.
+ *
+ * <p>When a is nullable and b is non-nullable, the {@code COALESCE(a,b)}
data type will be
+ * non-nullable and {@code CASE WHEN a is not null THEN a ELSE b} data type
will be nullable.
+ * The validator will throw an exception about failing to preserve the data
type.
+ *
+ * <p>Add CAST NOT NULL operator to {@code CASE WHEN a is not null THEN a
ELSE b},
+ * making it becomes
+ * <pre>{@code CASE WHEN a is not null THEN CAST NOT NULL(a) ELSE b}
+ * </pre>
+ *
+ * <p>Then the validator knows this data type is non-nullable.
+ * So we can keep the types consistent before and after conversion.
+ * */
+ public static final SqlOperator CAST_NOT_NULL =
+ new SqlInternalOperator("CAST NOT NULL", SqlKind.CAST_NOT_NULL, 2, true,
+ ReturnTypes.ARG0.andThen(SqlTypeTransforms.TO_NOT_NULLABLE), null,
+ OperandTypes.ANY) {
+ // This is an internal operator, which should not be unparsed to sql.
+ @Override public void unparse(SqlWriter writer, SqlCall call, int
leftPrec, int rightPrec) {
+ call.operand(0).unparse(writer, leftPrec, rightPrec);
+ }
+ };
+
/** Subject to change. */
private static class SqlBasicOperator extends SqlOperator {
@Override public SqlSyntax getSyntax() {
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 78f8865915..fc8bde9a84 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -295,6 +295,14 @@ private StandardConvertletTable() {
registerOp(SqlInternalOperators.MEASURE,
(cx, call) -> cx.convertExpression(call.operand(0)));
+ // Expand "CAST NOT NULL(x)" into "CAST(x AS INTEGER NOT NULL)"
+ registerOp(SqlInternalOperators.CAST_NOT_NULL,
+ (cx, call) ->
+ cx.getRexBuilder().makeCast(
+ cx.getTypeFactory().createTypeWithNullability(
+ cx.getValidator().getValidatedNodeType(call), false),
+ cx.convertExpression(call.operand(0))));
+
registerOp(SqlStdOperatorTable.CONVERT, this::convertCharset);
registerOp(SqlLibraryOperators.CONVERT_ORACLE, this::convertCharset);
registerOp(SqlStdOperatorTable.TRANSLATE, this::translateCharset);
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 50e5eec2ab..cc6a80488e 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -5227,6 +5227,18 @@ void checkUserDefinedOrderByOver(NullCollation
nullCollation) {
sql(sql).ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7044";>[CALCITE-7044]
+ * Add internal operator CAST NOT NULL to enhance rewrite COALESCE
operator</a>. */
+ @Test void testCoalesceSubquery() {
+ final String sql = "SELECT"
+ + " deptno, "
+ + " coalesce((select sum(empno) from emp "
+ + " where deptno = emp.deptno limit 1), 0) as w "
+ + "FROM dept";
+ sql(sql).ok();
+ }
+
/**
* Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-4145";>[CALCITE-4145]
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index e63683dd23..3de3b1a495 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -7225,7 +7225,7 @@ empnullables t2 on coalesce(t1.ename, t2.ename) = 'abc']]>
<Resource name="planBefore">
<![CDATA[
LogicalProject(DEPTNO=[$7])
- LogicalJoin(condition=[=(CASE(IS NOT NULL($1), $1, $10), 'abc')],
joinType=[inner])
+ LogicalJoin(condition=[=(CASE(IS NOT NULL($1), CAST($1):VARCHAR(20) NOT
NULL, $10), 'abc')], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 2dcbe1f413..0c7463f7b9 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -651,6 +651,29 @@ LogicalProject(EXPR$0=[CHAR_LENGTH('foo')])
<![CDATA[
LogicalProject(EXPR$0=[CASE(IS NOT NULL($3), CAST($3):INTEGER NOT NULL, 0)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testCoalesceSubquery">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, coalesce((select sum(empno) from emp where
deptno = emp.deptno limit 1), 0) as w FROM dept]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], W=[CASE(IS NOT NULL($2), CAST($3):INTEGER NOT
NULL, 0)])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalJoin(condition=[true], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(fetch=[1])
+ LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[=($7, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalSort(fetch=[1])
+ LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
+ LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[=($7, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -2098,7 +2121,7 @@ LogicalProject(EMPNO=[$0])
LogicalFilter(condition=[IS NOT NULL($9)])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$11])
LogicalJoin(condition=[=($9, $10)], joinType=[left])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[CASE(IS NOT
NULL($1), $1, 'M':VARCHAR(20))])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[CASE(IS NOT
NULL($1), CAST($1):VARCHAR(20) NOT NULL, 'M':VARCHAR(20))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject($f9=[CASE(IS NOT NULL($1), CAST($1):VARCHAR(20) NOT
NULL, 'M':VARCHAR(20))], $f0=[true])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 8a3787d07f..0646f2c3b5 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -1134,7 +1134,7 @@ EnumerableCalc(expr#0..3=[{inputs}], ENAME=[$t1],
DEEP2SAL=[$t3])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableAggregate(group=[{0}], EXPR$0=[SUM($1)])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t4)],
expr#6=[0.00:DECIMAL(19, 2)], expr#7=[CASE($t5, $t4, $t6)], expr#8=[+($t2,
$t7)], MGR9=[$t1], $f0=[$t8])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t4)],
expr#6=[CAST($t4):DECIMAL(19, 2) NOT NULL], expr#7=[0.00:DECIMAL(19, 2)],
expr#8=[CASE($t5, $t6, $t7)], expr#9=[+($t2, $t8)], MGR9=[$t1], $f0=[$t9])
EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)],
EMPNO=[$t0], MGR=[$t3], SAL=[$t5], $condition=[$t8])
EnumerableTableScan(table=[[scott, EMP]])
@@ -2714,7 +2714,7 @@ where exists
# following plan (two scans of EMP table instead of three).
EnumerableCalc(expr#0..2=[{inputs}], ENAME=[$t1])
EnumerableHashJoin(condition=[=($2, $3)], joinType=[semi])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)],
expr#9=[CAST($t3):INTEGER], expr#10=[0], expr#11=[CASE($t8, $t9, $t10)],
proj#0..1=[{exprs}], $f3=[$t11])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)],
expr#9=[CAST($t3):INTEGER NOT NULL], expr#10=[0], expr#11=[CASE($t8, $t9,
$t10)], proj#0..1=[{exprs}], $f3=[$t11])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)],
expr#9=[CAST($t3):INTEGER NOT NULL], expr#10=[0], expr#11=[CASE($t8, $t9,
$t10)], $f8=[$t11])
EnumerableTableScan(table=[[scott, EMP]])
