[jira] [Created] (CALCITE-6289) View in union cannot be reused

["yisha zhou (Jira)" <ji...@apache.org>]

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)