Uploaded image for project: 'Kylin'
  1. Kylin
  2. KYLIN-4464

Query ... row_number over(order by c1) ... order by c2 ... get wrong order result

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • v3.1.0, v3.0.2
    • Query Engine
    • None
    • Sprint 51

    Description

      SQL

      select t.*, row_number() over (order by t.lstg_format_name) as row_num from (
          select lstg_format_name, sum(price) as GMV from
              KYLIN_SALES
                where lstg_format_name is not null
                 group by lstg_format_name
      ) as t
      order by t.GMV desc limit 5{quote}
      

       

      Kylin result (order by lstg_format_name)

       
      Calcite execution plan

      EXECUTION PLAN BEFORE REWRITE
      OLAPToEnumerableConverter
        OLAPWindowRel(window#0=[window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])], ctx=[], groups=[[window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])]])
          OLAPLimitRel(ctx=[], fetch=[50000])
            OLAPSortRel(sort0=[$1], dir0=[DESC], ctx=[])
              OLAPAggregateRel(group=[{0}], GMV=[SUM($1)], ctx=[])
                OLAPProjectRel(LSTG_FORMAT_NAME=[$2], PRICE=[$5], ctx=[])
                  OLAPFilterRel(condition=[IS NOT NULL($2)], ctx=[])
                    OLAPTableScan(table=[[DEFAULT, KYLIN_SALES]], ctx=[], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]])
      

       

      Hive result(order by GMV)

      Attachments

        1. image-2020-04-20-18-19-40-925.png
          24 kB
          Chao Long
        2. image-2020-04-20-18-22-34-765.png
          25 kB
          Chao Long
        3. image-2020-05-07-10-32-39-983.png
          22 kB
          Chao Long

        Activity

          People

            Wayne0101 Chao Long
            Wayne0101 Chao Long
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: