[
https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-4323:
---------------------------------
Description:
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.
We should retain the ORDER BY of the view if it is used in the query at top
level (i.e. if the query contains expressions in the SELECT list and optionally
a WHERE clause, but not a GROUP BY, ORDER BY, HAVING, DISTINCT, aggregates,
UNION, and not if the view appears in a sub-query).
If the ORDER BY has FETCH or OFFSET, it should always be retained, because
those clauses affect which rows are returned, not just he order of rows.
was:
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.
We should
> If a view definition has an ORDER BY clause, retain the sort if the view is
> used in a query at top level
> --------------------------------------------------------------------------------------------------------
>
> 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
> Fix For: 1.30.0
>
> Attachments: image-2020-10-10-23-58-39-822.png,
> image-2020-10-10-23-59-22-297.png
>
> Time Spent: 1.5h
> 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.
> We should retain the ORDER BY of the view if it is used in the query at top
> level (i.e. if the query contains expressions in the SELECT list and
> optionally a WHERE clause, but not a GROUP BY, ORDER BY, HAVING, DISTINCT,
> aggregates, UNION, and not if the view appears in a sub-query).
> If the ORDER BY has FETCH or OFFSET, it should always be retained, because
> those clauses affect which rows are returned, not just he order of rows.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
