[calcite] branch main updated: [CALCITE-5134] Queries with subquery inside select list does not work if subquery uses table from left join

[ru...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

rubenql 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 de41df4d11 [CALCITE-5134] Queries with subquery inside select list does not work if subquery uses table from left join
de41df4d11 is described below

commit de41df4d117041fbee042e07f70e6043f1fe626d
Author: Benchao Li <li...@gmail.com>
AuthorDate: Thu May 5 22:00:32 2022 +0800

    [CALCITE-5134] Queries with subquery inside select list does not work if subquery uses table from left join
---
 .../org/apache/calcite/sql/validate/ListScope.java   | 18 ++++++++++++++++++
 .../apache/calcite/sql2rel/SqlToRelConverter.java    |  8 +++++++-
 .../apache/calcite/test/SqlToRelConverterTest.java   |  8 ++++++++
 .../apache/calcite/test/SqlToRelConverterTest.xml    | 20 ++++++++++++++++++++
 4 files changed, 53 insertions(+), 1 deletion(-)

diff --git a/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java b/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
index 656205b3c7..b86472d5b4 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
@@ -80,6 +80,24 @@ public abstract class ListScope extends DelegatingScope {
     return Util.transform(children, scopeChild -> scopeChild.name);
   }
 
+  /**
+   * Whether the ith child namespace produces nullable result.
+   *
+   * For example, in below query,
+   * <pre>
+   *   SELECT *
+   *   FROM EMPS
+   *   LEFT OUTER JOIN DEPT
+   * </pre>
+   * the namespace which corresponding to 'DEPT' is nullable.
+   *
+   * @param i The child index.
+   * @return Whether it's nullable.
+   */
+  public boolean isChildNullable(int i) {
+    return children.get(i).nullable;
+  }
+
   private @Nullable ScopeChild findChild(List<String> names,
       SqlNameMatcher nameMatcher) {
     for (ScopeChild child : children) {
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 166b593f1f..b0b7bdfa77 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -4911,7 +4911,13 @@ public class SqlToRelConverter {
           int i = 0;
           int offset = 0;
           for (SqlValidatorNamespace c : ancestorScope1.getChildren()) {
-            builder.addAll(c.getRowType().getFieldList());
+            if (ancestorScope1.isChildNullable(i)) {
+              for (final RelDataTypeField f : c.getRowType().getFieldList()) {
+                builder.add(f.getName(), typeFactory.createTypeWithNullability(f.getType(), true));
+              }
+            } else {
+              builder.addAll(c.getRowType().getFieldList());
+            }
             if (i == resolve.path.steps().get(0).i) {
               for (RelDataTypeField field : c.getRowType().getFieldList()) {
                 fields.put(field.getName(), field.getIndex() + offset);
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 f4954e6daa..b853bc3822 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -2639,6 +2639,14 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
     sql(sql).withExpand(false).ok();
   }
 
+  @Test void testCorrelatedForOuterFields() {
+    final String sql = "SELECT ARRAY(SELECT dept.deptno)\n"
+        + "FROM emp\n"
+        + "LEFT OUTER JOIN dept\n"
+        + "ON emp.empno = dept.deptno";
+    sql(sql).ok();
+  }
+
   /**
    * Test case for
    * <a href="https://issues.apache.org/jira/browse/CALCITE-614">[CALCITE-614]
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 82465eaad8..1a23e65d0e 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -624,6 +624,26 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 })])
   LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+    </Resource>
+  </TestCase>
+  <TestCase name="testCorrelatedForOuterFields">
+    <Resource name="sql">
+      <![CDATA[SELECT ARRAY(SELECT dept.deptno)
+FROM emp
+LEFT OUTER JOIN dept
+ON emp.empno = dept.deptno]]>
+    </Resource>
+    <Resource name="plan">
+      <![CDATA[
+LogicalProject(EXPR$0=[$11])
+  LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{9}])
+    LogicalJoin(condition=[=($0, $9)], joinType=[left])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+    Collect(field=[EXPR$0])
+      LogicalProject(DEPTNO=[$cor0.DEPTNO0])
+        LogicalValues(tuples=[[{ 0 }]])
 ]]>
     </Resource>
   </TestCase>