[
https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17473143#comment-17473143
]
Julian Hyde edited comment on CALCITE-4323 at 1/11/22, 8:13 PM:
----------------------------------------------------------------
[~jiajunbernoulli], Thanks for the PR. It looks good. Can you add tests for
where views are expanded and they are NOT the top node and therefore the
{{Sort}} should be removed, e.g.
* {{select * from v union all select * from t}}
* {{select * from ( select a, b from v) where condition}}
* {{select * from v order by a}}
* {{select * from t where exists (select * from v)}}
* {{select * from v, t}}
* {{select a, count\(*) from v group by a}}
Also some other cases where we may or may not want to keep the {{{}Sort{}}}:
* {{select * from v where condition}}
* {{select * from v limit 5}}
This is now looking like a feature request (which is good!) so let's change the
summary to something like 'If a view definition has an ORDER BY clause, retain
the sort if the view is used in a query at top level'.
was (Author: julianhyde):
[~jiajunbernoulli], Thanks for the PR. It looks good. Can you add tests for
where views are expanded and they are NOT the top node and therefore the
{{Sort}} should be removed, e.g.
* {{select * from v union all select * from t}}
* {{select * from ( select a, b from v) where condition}}
* {{select * from v order by a}}
* {{select * from t where exists (select * from v)}}
* {{select * from v, t}}
* {{select a, count(*) from v group by a}}
Also some other cases where we may or may not want to keep the {{{}Sort{}}}:
* {{select * from v where condition}}
* {{select * from v limit 5}}
This is now looking like a feature request (which is good!) so let's change the
summary to something like 'If a view definition has an ORDER BY clause, retain
the sort if the view is used in a query at top level'.
> View with ORDER BY throws AssertionError during view expansion
> --------------------------------------------------------------
>
> Key: CALCITE-4323
> URL: https://issues.apache.org/jira/browse/CALCITE-4323
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Jiatao Tao
> Priority: Major
> Labels: pull-request-available
> Attachments: image-2020-10-10-23-58-39-822.png,
> image-2020-10-10-23-59-22-297.png
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> If you have a view that has an ORDER BY clause, and try to execute a query on
> that view, SqlToRelConverter throws AssertionError during view expansion. For
> example,
> {noformat}
> create view v as select * from "EMPLOYEES" order by "deptno";
> select * from V;
> java.lang.AssertionError
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:634)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.requiredCollation(SqlToRelConverter.java:629)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:578)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.expandView(CalcitePrepareImpl.java:1072)
> at
> org.apache.calcite.plan.ViewExpanders$1.expandView(ViewExpanders.java:52)
> at
> org.apache.calcite.schema.impl.ViewTable.expandView(ViewTable.java:127)
> at org.apache.calcite.schema.impl.ViewTable.toRel(ViewTable.java:120)
> at
> org.apache.calcite.prepare.RelOptTableImpl.toRel(RelOptTableImpl.java:285)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.toRel(SqlToRelConverter.java:3605)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertIdentifier(SqlToRelConverter.java:2522)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2160)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2109)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2066)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:662)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:643)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3458)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:569)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:242)
> {noformat}
> I suspected that it would be a problem for when the view does not project the
> sort column(s), but it seems to be a problem even if the columns are
> projected.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
