[GitHub] [incubator-doris] spaces-X opened a new issue, #8917: [Bug] unstable results of intersect_count with order by

[GitBox <gi...@apache.org>]

spaces-X opened a new issue, #8917:
URL: https://github.com/apache/incubator-doris/issues/8917

   ### Search before asking
   
   - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues.
   
   
   ### Version
   
   version: 0.14
   
   I searched the key words `intersect_count` in our pr list but i did not find a fix pr.
   So i am not sure whether it can be reproduced in version 0.15 or current master branch.
   
   ### What's Wrong?
   
   I got an unstable results of intersect_count with order by statement.
   Details are shown in sql 1 and sql 2.
   
   
   For sql 1, the first row is not stable 
   
   <img width="832" alt="image" src="https://user-images.githubusercontent.com/21240668/162387506-6024c7c9-89c1-49e5-92ae-ef4d72318142.png">
   
   <img width="826" alt="image" src="https://user-images.githubusercontent.com/21240668/162387991-6fb1442b-7997-4be3-925c-9411659c593e.png">
   
   For sql 2, the results are more random
   For `name A` the result of its `rank`, `pay_uv` and `pay_1d_uv` are random:
   case 1:
   <img width="897" alt="image" src="https://user-images.githubusercontent.com/21240668/162389192-7bcf8935-3bbc-41b0-bf87-7ebcf3e8e195.png">
   case 2:
   <img width="866" alt="image" src="https://user-images.githubusercontent.com/21240668/162389775-6204725a-1e87-48ad-a25f-7b15003823d0.png">
   case 3:
   
   <img width="906" alt="image" src="https://user-images.githubusercontent.com/21240668/162390069-f2e453a3-938b-4182-950f-a7a103e7605b.png">
   
   
   
   
   
   ### What You Expected?
   
   The results of this kind of sql are in order and stable.
   
   ### How to Reproduce?
   
   sql 1: simply sql (reproduction with low frequancy):
   ```
   select '2022-03-21' as partition_date,
          name,
          intersect_count(user_id, partition_date, '2022-03-21') as pay_uv,
   from  t
   where partition_date in ('2022-03-21','2022-03-22','2022-03-23','2022-03-24')
   group by name
   order by partition_date,pay_uv desc
   ```
   
   sql 2:complicated sql (reproduction with high frequancy):
   
   ```
   select '2022-03-21' as partition_date,
          name,
          intersect_count(user_id, partition_date, '2022-03-21') as pay_uv,
          intersect_count(user_id, partition_date, '2022-03-21', '2022-03-22') as repay_1d_uv
   from
     (select partition_date,
             name,
             bitmap_union(user_id) as user_id
      from t
      where partition_date in ('2022-03-21','2022-03-22','2022-03-23','2022-03-24')
      group by partition_date,
              name) t
   group by name
   order by partition_date,
            pay_uv desc
   ```
   
   ### Anything Else?
   
   _No response_
   
   ### Are you willing to submit PR?
   
   - [X] 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-unsubscribe@doris.apache.org.apache.org

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


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