apache / apache/doris

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

Open
#61,228 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
15.9k
Forks
3.9k
Avg merge
2d 23h
Merged PRs (30d)
520

Description

### 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

4.0.1

### What's Wrong?

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

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

Image

Image
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

- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.