[KYLIN-4464] Query ... row_number over(order by c1) ... order by c2 ... get wrong order result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: v3.1.0, v3.0.2
• Component/s: Query Engine
• Sprint: Sprint 51
• Assignee: Chao Long
• Reporter: Chao Long
• Created: 20/Apr/20 10:24
• Updated: 10/Jul/20 12:23
• Resolved: 22/May/20 10:44

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)