A Beam user brought us a query that produces an invalid plan after
upgrading to a newer version of Beam (moving them from Calcite 1.20 to
1.28). I was able to write a test case which demonstrates this issue
with a trivial filter on a table containing a nested struct with a
single field. The issue appears to be coming out of
RelStructuredTypeFlattener.rewrite and I bisected it to a single
commit:
https://github.com/apache/calcite/commit/e44beba286ea9049c5fd00c3a3b0e4a4f1c03356
Removing the noFlatteningForInput function added in this commit fixes
the issue. That method doesn't appear to be looking at types at all,
only mismatches in field count, I expect so any single field struct
would hit this. Any suggestions on how we can work around this in
Beam?
The test query and plans are as follows, the test case is attached.
select dn.skill from sales.dept_single dn WHERE dn.skill.type = ''
Expected plan:
LogicalProject(SKILL=[ROW($0)])
LogicalFilter(condition=[=($0, '')])
LogicalProject(TYPE=[$0.TYPE])
LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])
Actual Plan:
LogicalProject(SKILL=[ROW($0)])
LogicalFilter(condition=[=($0.TYPE, '')])
LogicalProject(TYPE=[$0.TYPE])
LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])
Plan before flatten:
LogicalProject(SKILL=[$0])
LogicalFilter(condition=[=($0.TYPE, '')])
LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])
From e53ab9781ca2f8e5fdf1cd600317a01819557701 Mon Sep 17 00:00:00 2001
From: Andrew Pilloud <[email protected]>
Date: Wed, 1 Mar 2023 16:23:39 -0800
Subject: [PATCH] Demonstrate the wrong plan
SqlToRelConverterTest > executionError FAILED
java.lang.IllegalArgumentException: Actual and reference files differ. If you are adding new tests, replace the reference file with the current actual file, after checking its content.
diff /usr/local/google/home/apilloud/calcite/core/build/resources/test/org/apache/calcite/test/SqlToRelConverterTest_actual.xml /usr/local/google/home/apilloud/calcite/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
4778c4778
< LogicalFilter(condition=[=($0, '')])
---
> LogicalFilter(condition=[=($0.TYPE, '')])
at org.apache.calcite.test.DiffRepository.checkActualAndReferenceFiles(DiffRepository.java:261)
at org.apache.calcite.test.SqlToRelConverterTest.checkActualAndReferenceFiles(SqlToRelConverterTest.java:90)
---
.../apache/calcite/test/SqlToRelConverterTest.java | 6 ++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 14 ++++++++++++++
.../org/apache/calcite/test/catalog/Fixture.java | 3 +++
.../test/catalog/MockCatalogReaderSimple.java | 6 ++++++
4 files changed, 29 insertions(+)
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 60215c3e067..f5f2fb4be73 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3709,6 +3709,12 @@ void checkCorrelatedMapSubQuery(boolean expand) {
sql(sql).ok();
}
+ @Test void testNestedStructFieldAccessWhere() {
+ final String sql = "select dn.skill\n"
+ + "from sales.dept_single dn WHERE dn.skill.type = ''";
+ sql(sql).ok();
+ }
+
@Test void testNestedStructPrimitiveFieldAccess() {
final String sql = "select dn.skill['others']['a']\n"
+ "from sales.dept_nested dn";
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 1636bc7c17a..bfaeef7f40d 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4764,6 +4764,20 @@ from sales.dept_nested dn]]>
<![CDATA[
LogicalProject(EXPR$0=[ROW($2.OTHERS.A, $2.OTHERS.B)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNestedStructFieldAccessWhere">
+ <Resource name="sql">
+ <![CDATA[select dn.skill
+from sales.dept_single dn WHERE dn.skill.type = '']]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(SKILL=[ROW($0)])
+ LogicalFilter(condition=[=($0, '')])
+ LogicalProject(TYPE=[$0.TYPE])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT_SINGLE]])
]]>
</Resource>
</TestCase>
diff --git a/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java b/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
index 8a527b4b841..f1e97dc4699 100644
--- a/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
+++ b/testkit/src/main/java/org/apache/calcite/test/catalog/Fixture.java
@@ -68,6 +68,9 @@ final class Fixture extends AbstractFixture {
.build())
.kind(StructKind.PEEK_FIELDS_NO_EXPAND)
.build();
+ final RelDataType singleRecordType = typeFactory.builder()
+ .add("TYPE", varchar10Type)
+ .build();
final RelDataType abRecordType = typeFactory.builder()
.add("A", varchar10Type)
.add("B", varchar10Type)
diff --git a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
index 6171e69c7eb..e10ebde6413 100644
--- a/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
+++ b/testkit/src/main/java/org/apache/calcite/test/catalog/MockCatalogReaderSimple.java
@@ -158,6 +158,12 @@ protected MockCatalogReaderSimple(RelDataTypeFactory typeFactory,
deptTable.addColumn("NAME", fixture.varchar10Type);
registerTable(deptTable);
+ // Register "DEPT_SINGLE" table.
+ MockTable deptSingleTable =
+ MockTable.create(this, salesSchema, "DEPT_SINGLE", false, 4);
+ deptSingleTable.addColumn("SKILL", fixture.singleRecordType);
+ registerTable(deptSingleTable);
+
// Register "DEPT_NESTED" table.
MockTable deptNestedTable =
MockTable.create(this, salesSchema, "DEPT_NESTED", false, 4);
