[GitHub] [doris] Accelarator opened a new issue, #15951: [Bug] 基础函数rank()排序有bug

[GitBox]

Accelarator opened a new issue, #15951:
URL: https://github.com/apache/doris/issues/15951

   ### Search before asking
   
   - [X] I had searched in the 
[issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no 
similar issues.
   
   
   ### Version
   
   1.2.1
   
   ### What's Wrong?
   
   问题：同一user_id+不同priority，但是rk均为1的情况。每个aggregate_table每天数据量在500w左右
   问题结果：
   
![image](https://user-images.githubusercontent.com/18416349/212587069-40212bba-e589-43df-9329-3216a29c9767.png)
   
   建表语句：
   ```SQL
   create table if not exists aggregate_table_1(
        data_time  datetime             comment '数据时间，格式yyyy-MM-dd 00:00:00'
       ,user_id    varchar(64)          comment ''
       ,key2       varchar(64)          comment ''
       ,aggr1      varchar(64)  replace comment ''
       ,aggr2      bigint       max     comment ''
       ,aggr3      varchar(256) replace comment ''
       ,aggr4      varchar(64)  replace comment ''
       ,aggr5      varchar(64)  replace comment ''
       ,aggr6      varchar(64)  min     comment ''
       ,aggr7      varchar(64)  max     comment ''
       ,aggr8      varchar(64)  replace comment ''
       ,aggr9      varchar(64)  replace comment ''
   )
   engine=olap 
   aggregate key(data_time,user_id,key2) 
   comment "" 
   partition by range(data_time)() 
   distributed by hash(data_time，user_id) buckets 2 
   properties (
       'replication_num' = '3',
       'dynamic_partition.enable' = 'true',
       'dynamic_partition.time_unit' = 'day',
       'dynamic_partition.end' = '1',
       'dynamic_partition.prefix' = 'p',
       'dynamic_partition.buckets' = '2'
   );
   ```
   
   查询SQL：
   ```SQL
   select
       *
       ,rank() over(partition by user_id order by priority) as rk
   from
       (
           -- 设置优先级，优先级顺序1最高，8最低
           select 
               *, 1 as priority
           from
               aggregate_table_1
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 2 as priority
           from
               aggregate_table_2
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 3 as priority
           from
               aggregate_table_3
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 4 as priority
           from
               aggregate_table_4
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 5 as priority
           from
               aggregate_table_5
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 6 as priority
           from
               aggregate_table_6
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 7 as priority
           from
               aggregate_table_7
           where
               data_time = date_trunc(now(), 'day')
           union all
           select 
               *, 8 as priority
           from
               aggregate_table_8
           where
               data_time = date_trunc(now(), 'day')
       ) t
   ```
   
   
   ### What You Expected?
   
   查明原因，在修复前能否有替代方案。基础函数影响正常查询，挺急的
   
   ### How to Reproduce?
   
   _No response_
   
   ### Anything Else?
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [ ] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of 
Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org