Uploaded image for project: 'Calcite'
  1. Calcite
  2. 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

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 1.30.0
    • None

    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.

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            Aron.tao Jiatao Tao
            julianhyde Julian Hyde
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0h
                0h
                Logged:
                Time Spent - 1.5h
                1.5h

                Slack

                  Issue deployment