This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 a2f2a6f6d3 [CALCITE-7547] BIG_QUERY conformance should allow field
access on UNNEST(array_of_struct) AS alias
a2f2a6f6d3 is described below
commit a2f2a6f6d352f22d45919fdc34d1f7a2bf799b18
Author: Takaaki Nakama <[email protected]>
AuthorDate: Sun May 24 01:36:58 2026 +0900
[CALCITE-7547] BIG_QUERY conformance should allow field access on
UNNEST(array_of_struct) AS alias
Under SqlConformanceEnum.BIG_QUERY, SELECT i.name FROM t, UNNEST(t.items)
AS i
where items is ARRAY<ROW<name ...>> failed validation with
"Column 'NAME' not found in table 'I'" because allowAliasUnnestItems()
returned
false. With the flag off, SqlUnnestOperator#inferReturnType flattens the ROW
element type into individual columns instead of keeping it as a single
struct-typed column the alias can wrap, and AliasNamespace cannot remap the
flattened result back under the alias.
BigQuery itself supports this access pattern, so extend the existing PRESTO
case in SqlConformanceEnum#allowAliasUnnestItems() to also cover BIG_QUERY.
This complements CALCITE-7546, which removes the downstream NPE in
SqlToRelConverter#convertUnnest for the 2-operand AS(UNNEST, alias) form
when the flag is enabled.
---
.../calcite/sql/validate/SqlConformance.java | 1 +
.../calcite/sql/validate/SqlConformanceEnum.java | 1 +
.../apache/calcite/test/SqlToRelConverterTest.java | 26 +++++++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 31 ++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 34 ++++++++++++++++++++++
5 files changed, 93 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index fe902759db..fcd3b46641 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -375,6 +375,7 @@ default boolean isColonFieldAccessAllowed() {
* fields of T if T is a STRUCT type.
*
* <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#BIG_QUERY},
* {@link SqlConformanceEnum#PRESTO};
* false otherwise.
*/
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 047f05981a..f0f258171b 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -435,6 +435,7 @@ public enum SqlConformanceEnum implements SqlConformance {
@Override public boolean allowAliasUnnestItems() {
switch (this) {
+ case BIG_QUERY:
case PRESTO:
return true;
default:
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 9217e74895..4e8d17cd7a 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1941,6 +1941,32 @@ public static void checkActualAndReferenceFiles() {
sql(sql).withConformance(SqlConformanceEnum.PRESTO).ok();
}
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7547";>[CALCITE-7547]
+ * BIG_QUERY conformance should allow field access on
+ * UNNEST(array_of_struct) AS alias</a>.
+ */
+ @Test void testAliasUnnestArrayPlanWithSingleColumnBigQuery() {
+ final String sql = "select d.deptno, employee.empno\n"
+ + "from dept_nested_expanded as d,\n"
+ + " UNNEST(d.employees) as t(employee)";
+ sql(sql).withConformance(SqlConformanceEnum.BIG_QUERY).ok();
+ }
+
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7547";>[CALCITE-7547]
+ * BIG_QUERY conformance should allow field access on
+ * UNNEST(array_of_struct) AS alias</a>.
+ */
+ @Test void testAliasUnnestArrayPlanWithDoubleColumnBigQuery() {
+ final String sql = "select d.deptno, e, k.empno\n"
+ + "from dept_nested_expanded as d CROSS JOIN\n"
+ + " UNNEST(d.admins, d.employees) as t(e, k)";
+ sql(sql).withConformance(SqlConformanceEnum.BIG_QUERY).ok();
+ }
+
@Test void testArrayOfRecord() {
sql("select employees[1].detail.skills[2+3].desc from dept_nested").ok();
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 5198762c10..9a080efbf8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8816,6 +8816,37 @@ void testGroupExpressionEquivalenceParams() {
+ "\\('EMPNO', 'ENAME', 'DETAIL'\\), whereas alias list has 1
columns");
}
+ /**
+ * Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7547";>[CALCITE-7547]
+ * BIG_QUERY conformance should allow field access on
+ * UNNEST(array_of_struct) AS alias</a>.
+ */
+ @Test void testAliasUnnestMultipleArraysBigQuery() {
+ // for accessing a field in STRUCT type unnested from array
+ sql("select e.ENAME\n"
+ + "from dept_nested_expanded as d CROSS JOIN\n"
+ + " UNNEST(d.employees) as t(e)")
+ .withConformance(SqlConformanceEnum.BIG_QUERY).columnType("VARCHAR(10)
NOT NULL");
+
+ // for unnesting multiple arrays at the same time
+ sql("select d.deptno, e, k.empno, l.\"unit\", l.\"X\" * l.\"Y\"\n"
+ + "from dept_nested_expanded as d CROSS JOIN\n"
+ + " UNNEST(d.admins, d.employees, d.offices) as t(e, k, l)")
+ .withConformance(SqlConformanceEnum.BIG_QUERY).ok();
+
+ // Make sure validation fails properly given illegal select items
+ sql("select d.deptno, ^e^.some_column, k.empno\n"
+ + "from dept_nested_expanded as d CROSS JOIN\n"
+ + " UNNEST(d.admins, d.employees) as t(e, k)")
+ .withConformance(SqlConformanceEnum.BIG_QUERY)
+ .fails("Table 'E' not found");
+ sql("select d.deptno, e.detail, ^unknown^.detail\n"
+ + "from dept_nested_expanded as d CROSS JOIN\n"
+ + " UNNEST(d.employees) as t(e)")
+ .withConformance(SqlConformanceEnum.BIG_QUERY).fails("Incompatible
types");
+ }
+
@Test void testUnnestArray() {
sql("select*from unnest(array[1])")
.columnType("INTEGER NOT NULL");
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 56f169629a..5ee7180efa 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -341,6 +341,23 @@ LogicalProject(DEPTNO=[$0], E=[$5], EMPNO=[$6.EMPNO])
</Resource>
<Resource name="sql">
<![CDATA[select d.deptno, e, k.empno
+from dept_nested_expanded as d CROSS JOIN
+ UNNEST(d.admins, d.employees) as t(e, k)]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAliasUnnestArrayPlanWithDoubleColumnBigQuery">
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], E=[$5], EMPNO=[$6.EMPNO])
+ LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2,
3}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED_EXPANDED]])
+ Uncollect
+ LogicalProject(ADMINS=[$cor0.ADMINS], EMPLOYEES=[$cor0.EMPLOYEES])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="sql">
+ <![CDATA[select d.deptno, e, k.empno
from dept_nested_expanded as d CROSS JOIN
UNNEST(d.admins, d.employees) as t(e, k)]]>
</Resource>
@@ -358,6 +375,23 @@ LogicalProject(DEPTNO=[$0], EMPNO=[$5.EMPNO])
</Resource>
<Resource name="sql">
<![CDATA[select d.deptno, employee.empno
+from dept_nested_expanded as d,
+ UNNEST(d.employees) as t(employee)]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testAliasUnnestArrayPlanWithSingleColumnBigQuery">
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$0], EMPNO=[$5.EMPNO])
+ LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{2}])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED_EXPANDED]])
+ Uncollect
+ LogicalProject(EMPLOYEES=[$cor0.EMPLOYEES])
+ LogicalValues(tuples=[[{ 0 }]])
+]]>
+ </Resource>
+ <Resource name="sql">
+ <![CDATA[select d.deptno, employee.empno
from dept_nested_expanded as d,
UNNEST(d.employees) as t(employee)]]>
</Resource>
