yisha zhou created CALCITE-6289:
-----------------------------------
Summary: View in union cannot be reused
Key: CALCITE-6289
URL: https://issues.apache.org/jira/browse/CALCITE-6289
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.36.0
Reporter: yisha zhou
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:
{code:java}
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); {code}
And then add a test in `SqlToRelConverterTest`:
{code:java}
@Test void testView() {
final String sql = "select empno from EMP_VIEW\n"
+ "union all\n"
+ "select deptno from EMP_VIEW";
sql(sql).withExtendedTester().ok();
} {code}
You will get the plan:
{code:java}
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]]) {code}
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|https://issues.apache.org/jira/browse/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.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
