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

    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

        1. image-2020-10-10-23-58-39-822.png
          79 kB
          Jiatao Tao
        2. image-2020-10-10-23-59-22-297.png
          384 kB
          Jiatao Tao

        Issue Links

          Activity

            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