Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
1.36.0
-
None
-
None
Description
When union two different projection of same view, the view cannot be reuse. To reproduce the problem, please create a `MockRelViewTable` in `MockCatalogReaderExtended` use code below:
List<String> empModifiableViewNames5 = ImmutableList.of(salesSchema.getCatalogName(), salesSchema.getName(), "EMP_VIEW"); TableMacro empModifiableViewMacro5 = MockModifiableViewRelOptTable.viewMacro(rootSchema, "select EMPNO, DEPTNO, ENAME\n" + "from EMPDEFAULTS\n" + "group by EMPNO, DEPTNO, ENAME", empModifiableViewNames5.subList(0, 2), ImmutableList.of(empModifiableViewNames5.get(2)), true); TranslatableTable empModifiableView5 = empModifiableViewMacro5.apply(ImmutableList.of()); MockTable mockEmpViewTable5 = MockRelViewTable.create( (ViewTable) empModifiableView5, this, empModifiableViewNames5.get(0), empModifiableViewNames5.get(1), empModifiableViewNames5.get(2), false, 20, null); registerTable(mockEmpViewTable5);
And then add a test in `SqlToRelConverterTest`:
@Test void testView() {
final String sql = "select empno from EMP_VIEW\n"
+ "union all\n"
+ "select deptno from EMP_VIEW";
sql(sql).withExtendedTester().ok();
}
You will get the plan:
LogicalUnion(all=[true])
LogicalProject(EMPNO=[$0])
LogicalAggregate(group=[{0, 1, 2}])
LogicalProject(EMPNO=[$0], DEPTNO=[$7], ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
LogicalProject(DEPTNO=[$1])
LogicalAggregate(group=[{0, 1, 2}])
LogicalProject(EMPNO=[$0], DEPTNO=[$7], ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
Obviously, RelNode tree in the view is not reused. The root cause is that we expand the views in `SqlToRelConverter#convertIdentifier` . Therefore I suggest to reintroduce the `SqlToRelConverter.Config#isConvertTableAccess` which is removed in CALCITE-3801. So that we can expand view at the time we want, e.g. after divide the projections in union and logic in the view into two subgraph.