Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2798

Optimizer should remove ORDER BY in sub-query, provided it has no LIMIT or OFFSET

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.18.0
    • Fix Version/s: 1.19.0
    • Component/s: core
    • Labels:
      None

      Description

      The following SQL performs sort twice, however inner sort can be eliminated

      select * from (
        select * from "emps" 
      order by "emps"."deptno"
      ) order by 1 desc

      The same goes for (window calculation will sort on its own)

      select row_number() over (order by "emps"."deptno")  from (
        select * from "emps" 
      order by "emps"."deptno" desc
      )

      The same goes for SetOp (union, minus):

      select * from (
        select * from "emps" 
      order by "emps"."deptno"
      ) union select * from (
        select * from "emps" 
      order by "emps"."deptno" desc
      )

      There might be other cases like that (e.g. Aggregate, Join, Exchange, SortExchange)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                vladimirsitnikov Vladimir Sitnikov
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: