[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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.30.0
• Labels: pull-request-available
• Assignee: Jiatao Tao
• Reporter: Julian Hyde
• Created: 09/Oct/20 23:11
• Updated: 19/Mar/22 09:21
• Resolved: 16/Feb/22 21:36

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,

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)

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.

Issue Links

• is related to: CALCITE-819 Add a "contract" for the result of a relational expression
• links to: GitHub Pull Request #2679