[
https://issues.apache.org/jira/browse/CALCITE-6289?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17823474#comment-17823474
]
yisha zhou commented on CALCITE-6289:
-------------------------------------
Hey [~julianhyde] , firstly, to answer your question, what I expected is
reusing the plan nodes. However after projection pushdown rule applies, the
RelNode tree of the view will be different and hard to be reused.
I'm sorry for providing an example that's not so typical. Let's change the sql
of view into:
{code:java}
SELECT MAX(EMPNO) EMPNO, MAX(DEPTNO) DEPTNO, ENAME
FROM EMPDEFAULTS
GROUP BY ENAME{code}
And the query remains unchanged:
{code:java}
SELECT EMPNO FROM EMP_VIEW
UNION ALL
SELECT DEPTNO FROM EMP_VIEW{code}
the plan is
{code:java}
LogicalUnion(all=[true])
LogicalProject(EMPNO=[$1])
LogicalAggregate(group=[{0}], EMPNO=[MAX($1)], DEPTNO=[MAX($2)])
LogicalProject(ENAME=[$1], EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
LogicalProject(DEPTNO=[$2])
LogicalAggregate(group=[{0}], EMPNO=[MAX($1)], DEPTNO=[MAX($2)])
LogicalProject(ENAME=[$1], EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]]) {code}
If projection pushdown rule applies, it would be like:
{code:java}
LogicalUnion(all=[true])
LogicalProject(EMPNO=[$1])
LogicalAggregate(group=[{0}], EMPNO=[MAX($1)]])
LogicalProject(ENAME=[$1], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
LogicalProject(DEPTNO=[$2])
LogicalAggregate(group=[{0}], DEPTNO=[MAX($1)])
LogicalProject(ENAME=[$1], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]]) {code}
You can find that RelNode tree of the view changes, what we can reuse is only
the LogicalTableScan.
I think if we don't expand the view in SqlToRelConverter, and leave the output
plan as below:
{code:java}
LogicalUnion(all=[true])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP_VIEW]])
LogicalProject(DEPTNO=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP_VIEW]]) {code}
for `LogicalTableScan(table=[[CATALOG, SALES, EMP_VIEW]])`, there is only one
java instance here.
Then I can split those two projections and the to-be-reused view into two
subgraph, and expand this view instance only once, the input of
`LogicalProject(EMPNO=[$0])` and `LogicalProject(DEPTNO=[$1])` will be the same
RelNode tree.
The flow mentioned above may not be the best way to solve view reused issue,
but it works in my project.
Actually , another case is that I want insert the different selection of same
view into two different sinks(I haven't found how to describe this case in
Calcite, therefore I used Union to describe the issue).
> 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
> Priority: Major
>
> 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)
