[I] [Bug] incorrect result when query complex view [doris]

[via GitHub]

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

   ### 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
   
   doris-2.1.7-rc03
   
   ### What's Wrong?
   
   I have complex view, it create based multiple base tables and views. when I 
query the view, it result the incorrect result.
   But when I directly execute the SQL statement of the view itself., it 
returns the correct result. 
   
   
   
   
   
   ### What You Expected?
   
   The query yields 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
   
   ```sql
   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 
   
   ```sql
   > select * from edw.view_kf_service_bug
   
   | origin_money |total_money | cid                 |
   ---------------+-----------+--------------+
   |30000             |588              | AZ1N0M4T7V |
   
   ```
   
   ## execute sql of view itself
   
   ```sql
   > 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
   
   - [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