[
https://issues.apache.org/jira/browse/FLINK-14895?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Leonard Xu updated FLINK-14895:
-------------------------------
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：
{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}
correct 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}
our result:
{code:java}
||||||||1019789218.690000000000000000|1 //rollup row
||||||||3113996.920000000000000000|1
||||||||1628997.000000000000000000|2
||||||||596191.740000000000000000|3
{code}
was:
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}
> 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
> Priority: Major
> 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}
> correct 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}
> our 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)
