Uploaded image for project: 'Flink'
  1. Flink
  2. FLINK-14895

same rank in rollup row with group row that all group key are null

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 1.9.1
    • Fix Version/s: 1.10.0
    • Component/s: Table SQL / Planner
    • Labels:
      None

      Description

      Same rank in rollup row with group row that all group key are null, in tcp-ds query 67,  the rank of our result is incorrect with other DB system.

      SQL:

      select  *
      from (select i_category
                  ,i_class
                  ,i_brand
                  ,i_product_name
                  ,d_year
                  ,d_qoy
                  ,d_moy
                  ,s_store_id
                  ,sumsales
                  ,rank() over (partition by i_category order by sumsales desc) rk
            from (select i_category
                        ,i_class
                        ,i_brand
                        ,i_product_name
                        ,d_year
                        ,d_qoy
                        ,d_moy
                        ,s_store_id
                        ,sum(coalesce(ss_sales_price*ss_quantity,0)) sumsales
                  from store_sales
                      ,date_dim
                      ,store
                      ,item
             where  ss_sold_date_sk=d_date_sk
                and ss_item_sk=i_item_sk
                and ss_store_sk = s_store_sk
                and d_month_seq between 1200 and 1200+11
             group by  rollup(i_category, i_class, i_brand, i_product_name, d_year, d_qoy, d_moy,s_store_id))dw1) dw2
      where rk <= 100
      order by i_category
              ,i_class
              ,i_brand
              ,i_product_name
              ,d_year
              ,d_qoy
              ,d_moy
              ,s_store_id
              ,sumsales
              ,rk
      limit 100
      

      correct result:

      NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|596191.74|4
      NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|1628997.00|3
      NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|3113996.92|2
      NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|1019789218.69|1  //rollup row
      

      our result:

      ||||||||1019789218.690000000000000000|1  //rollup row
      ||||||||3113996.920000000000000000|1
      ||||||||1628997.000000000000000000|2
      ||||||||596191.740000000000000000|3
      

       

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Leonard Xu Leonard Xu
                Reporter:
                Leonard Xu Leonard Xu
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: