[I] [Bug] Doris returns incorrect query results when there are multiple window functions in the select and one of the window functions applies the topN optimization. [doris]

[via GitHub]

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

   ### 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
   
   I can reproduce it on versions 2.1.7 and 2.1.8, other versions have not been 
verified
   
   ### What's Wrong?
   
    It will return incorrect results when using the Nereids optimizer
   
   
   
   ### What You Expected?
   
   Nereids optimizer can return correct results
   
   ### How to Reproduce?
   
   create database tmp;
   
   CREATE TABLE tmp.`test_table` (
     `mop` varchar(200) NULL,
     `mis` int NULL,
     `result_cpv` double NULL
   ) ENGINE=OLAP
   DUPLICATE KEY(`mop`)
   DISTRIBUTED BY HASH(`mop`) BUCKETS 10
   PROPERTIES (
   "replication_allocation" = "tag.location.default: 3",
   "min_load_replica_num" = "-1",
   "is_being_synced" = "false",
   "storage_medium" = "hdd",
   "storage_format" = "V2",
   "inverted_index_storage_format" = "V1",
   "light_schema_change" = "true",
   "disable_auto_compaction" = "false",
   "enable_single_replica_compaction" = "false",
   "group_commit_interval_ms" = "10000",
   "group_commit_data_bytes" = "134217728"
   );
   
   
   insert into test_table values ("2024-08",3,23.82),
   ("2024-10",3,21.24),
   ("2024-04",12,NULL),
   ("2024-04",3,61.92),
   ("2024-11",3,12.45),
   ("2024-05",3,54.54),
   ("2024-06",3,41.52),
   ("2024-12",3,5.04),
   ("2024-07",3,25.38),
   ("2024-09",3,23.21),
   ("2024-03",3,115.28),
   ("2024-03",12,NULL);
   
   
   SELECT /*+set_var(enable_nereids_planner=false)*/
       mop,
       mis,
       case when mis=3 then result_cpv end as 3mis_cpv,
       case when mis=12 then result_cpv end as 12mis_cpv,
       rn
   FROM
       (
           SELECT
               mop,
               mis,
               round(sum(result_cpv) OVER(PARTITION BY mop order by mis),2) as 
result_cpv,
               row_number() over (PARTITION by mis order by mop desc) rn
           FROM
               tmp.test_table t
       ) t
   WHERE
       rn = 1
   order by
       mop,mis
   ;
   
![Image](https://github.com/user-attachments/assets/7fea4dac-1880-4b34-a07e-1b13186d3c45)
   
   
   SELECT /*+set_var(enable_nereids_planner=true)*/
       mop,
       mis,
       case when mis=3 then result_cpv end as 3mis_cpv,
       case when mis=12 then result_cpv end as 12mis_cpv,
       rn
   FROM
       (
           SELECT
               mop,
               mis,
               round(sum(result_cpv) OVER(PARTITION BY mop order by mis),2) as 
result_cpv,
               row_number() over (PARTITION by mis order by mop desc) rn
           FROM
               tmp.test_table t
       ) t
   WHERE
       rn = 1
   order by
       mop,mis
   ;
   
   
![Image](https://github.com/user-attachments/assets/d78c3816-b6cc-4e82-8c22-b770a2f3b473)
   
   
   
   ### 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-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