[GitHub] [incubator-doris] gj-zhang opened a new issue #8814: [Bug] min and avg window function not work well.

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

gj-zhang opened a new issue #8814:
URL: https://github.com/apache/incubator-doris/issues/8814


   ### 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
   
   0.15.0 trunk-a1d1bd8
   
   ### What's Wrong?
   
   min and avg window function result wrong.
   
   ### What You Expected?
   
   return the correct result.
   
   ### How to Reproduce?
   
   ```sql
   create table test_window_func
   (
       id string,
       grade string,
       class string,
       math_score int,
       english_score int
   ) ENGINE=OLAP
   UNIQUE KEY(`id`)
   COMMENT "OLAP"
   DISTRIBUTED BY HASH(`id`) BUCKETS 4
   PROPERTIES (
   "replication_allocation" = "tag.location.default: 3",
   "in_memory" = "false",
   "storage_format" = "V2"
   );
   
   insert into test_window_func
   values
   ('1', '一年级', '一班', null, 7),
          ('2', null, '一班', 8, 7),
          ('3', '一年级', '二班', 9, 9),
          ('4', '一年级', '一班', 10, 1),
          ('5', '一年级', '一班', 5, 3);
   
   sql1:
   
   select grade, class, math_score,
          min(coalesce(math_score,0)) over (partition by grade, class) as min_math
   from test_window_func
   where math_score is not null;
   
   +-----------+--------+------------+----------+
   | grade     | class  | math_score | min_math |
   +-----------+--------+------------+----------+
   | 一年级    | 二班   |          9 |        9 |
   | NULL      | 一班   |          8 |        8 |
   | 一年级    | 一班   |          5 |        5 |
   | 一年级    | 一班   |         10 |        5 |
   +-----------+--------+------------+----------+
   
   sql2:
   
   select grade, class,math_score,
          min(coalesce(math_score,0)) over (partition by grade, class) as min_math,
          avg(coalesce(math_score, 0)) over (partition by grade, class) as avg_math
   from test_window_func
   where math_score is not null;
   
   +-----------+--------+------------+----------+----------+
   | grade     | class  | math_score | min_math | avg_math |
   +-----------+--------+------------+----------+----------+
   | 一年级    | 二班   |          9 |        0 |        9 |
   | NULL      | 一班   |          8 |        0 |        8 |
   | 一年级    | 一班   |         10 |        0 |      7.5 |
   | 一年级    | 一班   |          5 |        0 |      7.5 |
   +-----------+--------+------------+----------+----------+
   ```
   
   the result column min_math in sql2 is 0. not expected.
   
   ### Anything Else?
   
   nothing
   
   ### 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-unsubscribe@doris.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