[Bug] why the same sql select the result is different ,MATERIALIZED problem

[Jack-Qin-King]

Search before asking

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

Version

4.0.1

What's Wrong?

相同的SQL查询 条件分开查 和一起查为啥返回的结果不一样呀
找到问题了,是命中物化视图导致的,但有个问题, 使用了物化视图不存在的字段为啥还能命中,这个很奇怪

物化视图是汇总的表,没有包含明细的字段,但SQL查询时使用 条件 in (一个固定值) 就会误导使用物化视图,但物化视图没有这个字段,统计的就是所有数据,但是 条件 in(多个值 )不会使用物化视图,走的基表,因此数据也是正确

SQL EX: `select date_format(analysis_date, 'yyyy-MM-dd') as d5_analysis_date, cast(nvl(sum(cli_non_sub_pay_amount),0)/100+nvl(sum(ad_revenue),0)+nvl(sum(srv_sub_pay_amount),0)/100 as decimal(18,2)) as total_revenue, count(1)over() as total_cnt from dws_data.dws_t85_reelshort_srv_order_detail_stat_di_v2 t1

where etl_date between '2026-03-01' and '2026-03-01'
AND country_graded in ('T5','T4','T3')
group by d5_analysis_date
order by d5_analysis_date DESC;

select
date_format(analysis_date, 'yyyy-MM-dd') as d5_analysis_date,country_graded,
cast(nvl(sum(cli_non_sub_pay_amount),0)/100+nvl(sum(ad_revenue),0)+nvl(sum(srv_sub_pay_amount),0)/100 as decimal(18,2)) as total_revenue,
count(1)over() as total_cnt
from dws_data.dws_t85_reelshort_srv_order_detail_stat_di_v2 t1

where etl_date between '2026-03-01' and '2026-03-01'

group by d5_analysis_date,country_graded
order by d5_analysis_date DESC;`

What You Expected?

结果相同

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

[ixzc]

there will be transparent rewriting of Doris materialized views, so you can check that. btw, for the different result, is it because the materialized view's data isn't refreshed?
https://doris.incubator.apache.org/docs/4.x/query-acceleration/materialized-view/async-materialized-view/functions-and-demands#transparent-query-rewriting

[Jack-Qin-King]

i check the materialized view has refreshed, absolutely, i use two kind of sql which i use trim function, this kind sql will not choose
mv so the result is right ,so i think when choose mv Something went wrong.