[jira] [Closed] (CALCITE-4323) If a view definition has an ORDER BY clause, retain the sort if the view is used in a query at top level

["Liya Fan (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4323?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Liya Fan closed CALCITE-4323.
-----------------------------

Resolved in release 1.30.0 (2022-03-20)

> 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)