This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 60e1b72fa1 [CALCITE-6978] Translation to relational algebra of
correlated query with COALESCE causes an assertion failure
60e1b72fa1 is described below
commit 60e1b72fa164f0e06740bd3bf3f46a9725a87ab8
Author: Mihai Budiu <[email protected]>
AuthorDate: Thu May 1 12:43:14 2025 -0700
[CALCITE-6978] Translation to relational algebra of correlated query with
COALESCE causes an assertion failure
Signed-off-by: Mihai Budiu <[email protected]>
---
.../apache/calcite/sql/validate/SqlValidatorImpl.java | 4 +++-
.../org/apache/calcite/test/SqlToRelConverterTest.java | 12 ++++++++++++
.../org/apache/calcite/test/SqlToRelConverterTest.xml | 17 +++++++++++++++++
core/src/test/resources/sql/sub-query.iq | 2 +-
4 files changed, 33 insertions(+), 2 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index dae940982a..ba2330dce5 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -1528,7 +1528,9 @@ private void handleOffsetFetch(@Nullable SqlNode offset,
@Nullable SqlNode fetch
((SqlBasicCall) call).setOperator(overloads.get(0));
}
}
- if (config.callRewrite()) {
+ if (config.callRewrite()
+ // Do not rewrite calls that contain subqueries
+ && !SqlUtil.containsCall(call, c -> c.getKind() == SqlKind.SELECT)) {
node = call.getOperator().rewriteCall(this, call);
}
} else if (node instanceof SqlNodeList) {
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..41b1f0d757 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1678,6 +1678,18 @@ public static void checkActualAndReferenceFiles() {
sql(sql).ok();
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6978";>[CALCITE-6978]
+ * Translation to relational algebra of correlated query with COALESCE
causes an
+ * assertion failure</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 void testSampleBernoulliQuery() {
final String sql = "select * from (\n"
+ " select * from emp as e tablesample bernoulli(10) repeatable(1)\n"
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..1008b04c9f 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,23 @@ 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=[COALESCE($2, 0)])
+ 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]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 95734e88f2..59968cdec3 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -1108,7 +1108,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=[0], expr#6=[COALESCE($t4,
$t5)], expr#7=[+($t2, $t6)], MGR9=[$t1], $f0=[$t7])
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]])
