物化视图未生效

[banyue0618]

create materialized view dms_cargo_baseinfo_amt as
SELECT
	bitmap_count(bitmap_union(bitmap_hash(`cargocatg`))),
	bitmap_count(bitmap_union(bitmap_hash(`brandname`))),
	bitmap_count(bitmap_union(bitmap_hash(`cargoname`)))
FROM
	`dms_cargo_baseinfo`

通过命令查看物化视图，发现报错了
SHOW ALTER TABLE MATERIALIZED VIEW FROM gtc_bi_sink;
报错信息：
errCode = 2, detailMessage = rollup tasks failed on same tablet reach threshold [[ALTER], signature: 658899, backendId: 10003, tablet id: 658899], reason=task type: ALTER, status_code: INTERNAL_ERROR, status_message: [(10.10.104.55)[INTERNAL_ERROR]Unknown expr node type: 0], backendId: Backend [id=10003, host=10.10.104.55, heartbeatPort=9050, alive=true, lastStartTime=2023-11-21 10:07:48, tags: {location=default}], signature: 658899

[yagagagaga]

What is your version of Doris? You can upgrade to the latest version or try again.

[banyue0618]

version doris-2.0.0-rc04(SSE4.2) RELEASE，这几乎已经是最新版本的doris。让我感到更奇怪的是这个错误也太笼统了，我可能需要查看下fe的日志。

[yagagagaga]

You can upgrade to 2.0.4, 2.0.0 is not a stable version.

[banyue0618]

I have upgraded the Doris version, successfully created the materialized view afterwards, but when I checked the execution plan using explain, it showed that the materialized view was not being utilized.

CREATE MATERIALIZED VIEW dms_cargo_baseinfo_ts AS SELECT bitmap_count(bitmap_union(bitmap_hash(cargocatg))) AS cargocatg_count, bitmap_count(bitmap_union(bitmap_hash(brandname))) AS brandname_count, bitmap_count(bitmap_union(bitmap_hash(cargoname))) AS cargoname_count FROM dms_cargo_baseinfo;

explain SELECT bitmap_count(bitmap_union(bitmap_hash(cargocatg))) AS cargocatg_count, bitmap_count(bitmap_union(bitmap_hash(brandname))) AS brandname_count, bitmap_count(bitmap_union(bitmap_hash(cargoname))) AS cargoname_count FROM dms_cargo_baseinfo;

explain SELECT count(distinct cargocatg) AS catgCount, count(distinct brandname) AS brandCount, count(distinct cargoname) AS skuCount FROM dms_cargo_baseinfo

[BiteTheDDDDt]

create table dms_cargo_baseinfo (id int , cargocatg varchar(32), brandname varchar(32), cargoname varchar(32) ) distributed by hash(id) buckets 1 properties ("replication_num"="1");

CREATE MATERIALIZED VIEW dms_cargo_baseinfo_ts AS 
SELECT id,bitmap_union(bitmap_hash(cargocatg)), bitmap_union(bitmap_hash(brandname)), bitmap_union(bitmap_hash(cargoname)) 
FROM dms_cargo_baseinfo group by id;

insert into dms_cargo_baseinfo values (1, 'a', 'b', 'c');

explain
SELECT bitmap_count(bitmap_union(bitmap_hash(cargocatg))) AS cargocatg_count, bitmap_count(bitmap_union(bitmap_hash(brandname))) AS brandname_count, bitmap_count(bitmap_union(bitmap_hash(cargoname))) AS cargoname_count 
FROM dms_cargo_baseinfo;

把语句修改一下就可以了，create mv目前不支持在聚合函数(语句中的bitmap_union)外做计算(bitmap_count)的写法，后续我会添加一些这方面的检查。
另外，agg mv必须至少有一个key列(与普通聚合模型的表一样)，所以需要添加一个group by id。

[banyue0618]

之前最后我的实现方式还是用了唯一模型，然后去distinct查询。现在为了优化查询性能我又重新回到了这个问题。按照id去group之后，视图确实创建成功了，但是用explain看分析结果也没有用到物化视图 。

查询的时候我尝试加上group by id，但是也还是没用到物化视图。

[BiteTheDDDDt]

dup表和uniq表是不一样的