Doris命中物化视图导致查询数据异常

Viewed 88
2.1 query

doris version：2.1.2
物化视图（没有sum,count等聚合操作）：

create materialized view app_hotel_supplier_product_d0_d1_comparison_d_mv_2 as
select day_num
,supplier
,supplier_code
,country_code
,supplier_hotel_id
from app.app_hotel_supplier_product_d0_d1_comparison_d
group by day_num
,supplier
,supplier_code
,country_code
,supplier_hotel_id

image.png

Jrebel2
2 Answers

删除物化视图后查询正常？能否提供下这几个信息：

  1. 建表语句
  2. 查询物化视图的 explain
  3. 删除物化视图后同样sql的 explain
阿渊@SelectDB2896 
CREATE TABLE IF NOT EXISTS test.test_mv_bug
(
id int not null,
name varchar(100) not null ,
age int not null ,
sex int null,
day_num DATE
) ENGINE=OLAP
DUPLICATE KEY(id)
COMMENT "测试物化视图bug"
PARTITION BY RANGE(day_num)()
DISTRIBUTED BY HASH(id)
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "3",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "5",
"dynamic_partition.start" = "-4",
"dynamic_partition.end" = "1",
"storage_format" = "V2"
);
-- 创建物化视图
create materialized view test_mv_bug_mv_ss_2 as
select day_num,age,sex
from test.test_mv_bug
GROUP BY day_num,age,sex;

insert into `test`.`test_mv_bug`  values(1,'zhangsan',1,0,'2024-05-20'),(2,'wangwu',1,0,'2024-05-20'),(3,'zhaoliu',1,0,'2024-05-20'),(4,'tianqi',2,0,'2024-05-20'),(5,'laoba',2,0,'2024-05-20'),(6,'lijiu',2,0,'2024-05-20');

select count(*) from test.test_mv_bug where day_num='2024-05-20';

这样子可以复现这个问题
image.png

Jrebel2