[Bug] incorrect result when query complex view

[wgzhao]

Search before asking

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

Version

doris-2.1.7-rc03

What's Wrong?

This complex view, derived from multiple base tables and other views, produces incorrect results when queried directly. However, when the SQL statement defining the view is executed independently, it returns the correct results.

What You Expected?

The query returns the same results, whether querying the view or directly querying the SQL statement of the view itself.

How to Reproduce?

create view

I have create the following view

create or replace view edw.view_kf_service_bug as
with t0 as
(select `cid`, `origin_money`,`total_money`,
row_number() OVER (PARTITION by `cid`,cust_type, system_num  ORDER BY `spec_end_time` desc) AS group_num,
row_number() OVER (PARTITION by `cid` ORDER BY `spec_end_time` desc) AS num
from edw.view_kf_service_order_detaial_bug)
select 
t2.`origin_money`,
t2.`total_money`,
t11.`cid`
from
(select *
from t0
where num=1)t11
left join 
(select `cid`, 
sum(`origin_money`) as `origin_money`,
sum(`total_money`) as `total_money`
from t0
where group_num=1
group by `cid`)t2
on t11.`cid`=t2.`cid`
where t11.`cid` = 'AZ1N0M4T7V';

query the view

> select * from edw.view_kf_service_bug

| origin_money |total_money | cid                 |
---------------+-----------+--------------+
|30000             |588              | AZ1N0M4T7V |

execute sql of view itself

> with t0 as
(select `cid`, `origin_money`,`total_money`,
row_number() OVER (PARTITION by `cid`,cust_type, system_num  ORDER BY `spec_end_time` desc) AS group_num,
row_number() OVER (PARTITION by `cid` ORDER BY `spec_end_time` desc) AS num
from edw.view_kf_service_order_detaial_bug)
select 
t2.`origin_money`,
t2.`total_money`,
t11.`cid`
from
(select *
from t0
where num=1)t11
left join 
(select `cid`, 
sum(`origin_money`) as `origin_money`,
sum(`total_money`) as `total_money`
from t0
where group_num=1
group by `cid`)t2
on t11.`cid`=t2.`cid`
where t11.`cid` = 'AZ1N0M4T7V';

| origin_money |total_money | cid                 |
---------------+-----------+--------------+
| 330000           | 34176         | AZ1N0M4T7V |

Anything Else?

I have 1 FE and 3 BE, the storage are local disks and the OS is CentOS 7.9

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

[ixzc]

can you provide your table schema and data to let us reproduce this