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 940cac570c [CALCITE-7009] AssertionError when converting query
containing multiple correlated subqueries referencing different tables in FROM
940cac570c is described below
commit 940cac570c704c344aefa6eb09fbacb267d9cc3d
Author: Konstantin Orlov <[email protected]>
AuthorDate: Wed May 14 14:41:41 2025 +0300
[CALCITE-7009] AssertionError when converting query containing multiple
correlated subqueries referencing different tables in FROM
---
.../apache/calcite/sql2rel/SqlToRelConverter.java | 16 ---------------
.../apache/calcite/test/SqlToRelConverterTest.java | 8 ++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 24 ++++++++++++++++++++++
core/src/test/resources/sql/scalar.iq | 17 +++++++++++++++
4 files changed, 49 insertions(+), 16 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 1a0b298ed0..5ea3f997f7 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -3110,12 +3110,6 @@ protected RelNode createAsofJoin(
final ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
final List<CorrelationId> correlNames = new ArrayList<>();
- // All correlations must refer the same namespace since correlation
- // produces exactly one correlation source.
- // The same source might be referenced by different variables since
- // DeferredLookups are not de-duplicated at create time.
- SqlValidatorNamespace prevNs = null;
-
for (CorrelationId correlName : correlatedVariables) {
DeferredLookup lookup =
requireNonNull(mapCorrelToDeferred.get(correlName),
@@ -3131,7 +3125,6 @@ protected RelNode createAsofJoin(
nameMatcher, false, resolved);
assert resolved.count() == 1;
final SqlValidatorScope.Resolve resolve = resolved.only();
- final SqlValidatorNamespace foundNs = resolve.namespace;
final RelDataType rowType = resolve.rowType();
final int childNamespaceIndex = resolve.path.steps().get(0).i;
final SqlValidatorScope ancestorScope = resolve.scope;
@@ -3141,15 +3134,6 @@ protected RelNode createAsofJoin(
continue;
}
- if (prevNs == null) {
- prevNs = foundNs;
- } else {
- assert prevNs == foundNs : "All correlation variables should resolve"
- + " to the same namespace."
- + " Prev ns=" + prevNs
- + ", new ns=" + foundNs;
- }
-
int namespaceOffset = 0;
if (childNamespaceIndex > 0) {
// If not the first child, need to figure out the width
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 96d956a040..02afa42041 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1896,6 +1896,14 @@ void checkCorrelatedMapSubQuery(boolean expand) {
sql(sql).withExpand(false).ok();
}
+ @Test void
testMultipleCorrelatedSubQueriesInSelectReferencingDifferentTablesInFrom() {
+ final String sql = "select\n"
+ + "(select ename from emp where empno = empnos.empno) as emp_name,\n"
+ + "(select name from dept where deptno = deptnos.deptno) as
dept_name\n"
+ + " from (values (1), (2)) as empnos(empno), (values (1), (2)) as
deptnos(deptno)";
+ sql(sql).withExpand(false).ok();
+ }
+
@Test void testExists() {
final String sql = "select*from emp\n"
+ "where exists (select 1 from dept where deptno=55)";
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 760d4c5465..3c3c646159 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -5030,6 +5030,30 @@ LogicalProject(ENAME=[$cor0.ENAME])
}), 0)])
LogicalProject(DEPTNO=[$7], ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase
name="testMultipleCorrelatedSubQueriesInSelectReferencingDifferentTablesInFrom">
+ <Resource name="sql">
+ <![CDATA[select
+(select ename from emp where empno = empnos.empno) as emp_name,
+(select name from dept where deptno = deptnos.deptno) as dept_name
+ from (values (1), (2)) as empnos(empno), (values (1), (2)) as
deptnos(deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMP_NAME=[$SCALAR_QUERY({
+LogicalProject(ENAME=[$1])
+ LogicalFilter(condition=[=($0, $cor0.EMPNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], DEPT_NAME=[$SCALAR_QUERY({
+LogicalProject(NAME=[$1])
+ LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 1 }, { 2 }]])
+ LogicalValues(tuples=[[{ 1 }, { 2 }]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/scalar.iq
b/core/src/test/resources/sql/scalar.iq
index 0f26eb1bdd..05a5277137 100644
--- a/core/src/test/resources/sql/scalar.iq
+++ b/core/src/test/resources/sql/scalar.iq
@@ -301,4 +301,21 @@ from (values (1), (3)) t1(id);
!ok
+# Several scalar sub-queries reference different tables in FROM list
+select
+ (select ename from emp where empno = empnos.empno) as emp_name,
+ (select dname from dept where deptno = deptnos.deptno) as dept_name
+ from (values (7369), (7499)) as empnos(empno), (values (10), (20)) as
deptnos(deptno) order by 1, 2;
++----------+------------+
+| EMP_NAME | DEPT_NAME |
++----------+------------+
+| ALLEN | ACCOUNTING |
+| ALLEN | RESEARCH |
+| SMITH | ACCOUNTING |
+| SMITH | RESEARCH |
++----------+------------+
+(4 rows)
+
+!ok
+
# End scalar.iq
