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 <libenc...@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>