Leonard Xu created FLINK-14895:
----------------------------------
Summary: same rank in rollup row with group row that all group key
are null
Key: FLINK-14895
URL: https://issues.apache.org/jira/browse/FLINK-14895
Project: Flink
Issue Type: Bug
Components: Table SQL / Planner
Affects Versions: 1.9.1
Reporter: Leonard Xu
Fix For: 1.10.0
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：
{code:java}
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
{code}
our result:
{code:java}
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
{code}
correct result:
{code:java}
||||||||1019789218.690000000000000000|1 //rollup row
||||||||3113996.920000000000000000|1
||||||||1628997.000000000000000000|2
||||||||596191.740000000000000000|3
{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
