[Bug] sql result error

[luoyh]

Search before asking

• I had searched in the issues and found no similar issues.

Version

doris-2.1.6-rc04-653e315ba5

What's Wrong?

mysql> 
with t0 as (
            select 1 id, 2 val, 0 sort
  UNION ALL select 1, 3, 1
  union all select 1, 4, 2
  union all select 2, 1, 0
  union all select 2, 7, 1
)
, t1 as (
  select 
    id,
    sum(val) over(
      partition by id
      order by sort 
      rows between UNBOUNDED PRECEDING and current row
    ) calc,
    ROW_NUMBER() over(partition by id order by sort desc ) rn
  from t0
)

select * from t1 ;

+----+------+----+
| id | calc | rn |
+----+------+----+
|  1 |    9 |  1 |
|  1 |    5 |  2 |
|  1 |    2 |  3 |
|  2 |    8 |  1 |
|  2 |    1 |  2 |
+----+------+----+

-- add where rn=1 

mysql> 
with t0 as (
            select 1 id, 2 val, 0 sort
  UNION ALL select 1, 3, 1
  union all select 1, 4, 2
  union all select 2, 1, 0
  union all select 2, 7, 1
)
, t1 as (
  select 
    id,
    sum(val) over(
      partition by id
      order by sort 
      rows between UNBOUNDED PRECEDING and current row
    ) calc,
    ROW_NUMBER() over(partition by id order by sort desc ) rn
  from t0
)

select * from t1 
where rn = 1;
+----+------+----+
| id | calc | rn |
+----+------+----+
|  1 |    4 |  1 |
|  2 |    7 |  1 |
+----+------+----+

add where rn=1, the result has error.

What You Expected?

the correct result should be:

+----+------+----+
| id | calc | rn |
+----+------+----+
|  1 |    9 |  1 |
|  2 |    8 |  1 |
+----+------+----+

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

• I agree to follow this project's Code of Conduct