【已解决】2.1.5 查询sql使用IF不能命中异步物化视图

Viewed 150
mtmv 2.1

建表语句：
CREATE TABLE test (
a TINYINT NULL DEFAULT "0",
b INT NULL DEFAULT "0",
c INT NULL DEFAULT "0",
t DATETIME NOT NULL COMMENT '',
d BIGINT SUM NULL DEFAULT "0",
e BIGINT SUM NULL DEFAULT "0"
)ENGINE = OLAP AGGREGATE KEY
(a,b,c,t)
PARTITION BY RANGE(t) ()
DISTRIBUTED BY random buckets 1
PROPERTIES
(
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "HOUR",
"dynamic_partition.start" = "-3",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "1",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "3",
"light_schema_change" = "true"
);

随便插入一条记录：
insert into test VALUES (1,1,1,'2024-07-27 19:30:00', 11, 11);

创建异步物化视图：

CREATE MATERIALIZED VIEW mv_test
BUILD IMMEDIATE
REFRESH AUTO
ON SCHEDULE EVERY 1 MINUTE
PARTITION BY (t)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
"partition_sync_limit" = "1",
"grace_period"="300",
"partition_date_format" = "%Y%m%d%H",
"replication_num" = "2"
)
AS
SELECT a,b,t,SUM(d),SUM(e) FROM test GROUP BY 1,2,3;

查看命中情况： explain select a,t, SUM(d), SUM(e) FROM test WHERE t <= '2024-07-27 19:30:00' GROUP BY 1,2;

image.png

提示 MaterializedViewRewriteSuccessButNotChose

执行 explain select a,t, SUM(IF(a=0,d,e)) FROM test WHERE t <= '2024-07-27 19:30:00' GROUP BY 1,2; (使用IF函数):
image.png
提示 MaterializedViewRewriteFail: FailSummary: View struct info is invalid, View dimensions doesn't not cover the query dimensions, Query function roll up fail, Query struct info is invalid

有几个疑问：

  1. MaterializedViewRewriteSuccessAndChose 和 MaterializedViewRewriteSuccessButNotChose 分别在什么情况下出现？
  2. 异步物化视图透明改写是否不支持 IF 函数， 异步物化视图都已经覆盖查询需要的所有字段， 为什么还会报这个错。
某某某某23
1 Answers

1、改写成功并且命中了，是 CBO模型最后没有选。
对于单表小数据量的聚合，可能查询原表效果更好。
可以analyze table with sync 采集下统计信息试试是否有改写命中并且选中？
具体文档地址 https://doris.apache.org/zh-CN/docs/query/view-materialized-view/query-async-materialized-view#%E8%BE%85%E5%8A%A9%E5%8A%9F%E8%83%BD

MaterializedViewRewriteSuccessAndChose：透明改写成功，并且 CBO 选择的物化视图名称列表。

MaterializedViewRewriteSuccessButNotChose：透明改写成功，但是最终 CBO 没有选择的物化视图名称列表。

MaterializedViewRewriteFail：列举透明改写失败及原因摘要。

2、如果查询如下，想命中物化视图，那么物化中要有SUM(IF(a=0,d,e)) 聚合函数才可以，这样才符合物化视图透明改写原则

explain select a,t, SUM(IF(a=0,d,e)) FROM test WHERE t <= '2024-07-27 19:30:00' GROUP BY 1,2;
张彬华5265