【详述】同步物化视图在SUM中条件判断失效
【业务影响】查询结果错误
【是否存算分离】否
【StarRocks版本】3.2.6
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】dianbo.ruan@adtiming.com
【附件】
drop table oks.rdb_test1;
CREATE TABLE oks.rdb_test1 (
a datetime NULL COMMENT “”,
b int(11) NULL COMMENT “”,
c int(11) NULL COMMENT “”,
cnt1 bigint(20) SUM NULL COMMENT “”,
cnt2 bigint(20) SUM NULL COMMENT “”,
cnt3 bigint(20) SUM NULL COMMENT “”
) ENGINE=OLAP
AGGREGATE KEY(a, b, c)
COMMENT “OLAP”
PARTITION BY date_trunc(‘hour’, a)
DISTRIBUTED BY HASH(a)
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“enable_persistent_index” = “false”,
“replicated_storage” = “true”,
“partition_live_number” = “8760”,
“compression” = “LZ4”
);
INSERT into oks.rdb_test1 values (‘2024-05-29 00:00:00’, 1,1, 100,200,300);
INSERT into oks.rdb_test1 values (‘2024-05-29 00:00:00’, 1,2, 100,200,300);
SELECT
a,
SUM(cnt1) as cnt1,
SUM(IF(c = 1, cnt1, 0)) as cnt2,
SUM(IF(c = 2, cnt1, 0)) as cnt3
from
oks.rdb_test1
group by
a;
正确结果：2024-05-29 00:00:00 200 100 100
CREATE MATERIALIZED VIEW rdb_test_mv AS
SELECT a,
c,
SUM(cnt1) AS cnt1,
SUM(cnt2) AS cnt2,
SUM(cnt3) AS cnt3
FROM oks.rdb_test1
GROUP BY
a,c;
SELECT
a,
SUM(cnt1) as cnt1,
SUM(IF(c = 1, cnt1, 0)) as cnt2,
SUM(IF(c = 2, cnt1, 0)) as cnt3
from
oks.rdb_test1
group by
a;
错误结果：2024-05-29 00:00:00 200 200 200
全部加上条件判断又正确
SELECT
a,
SUM(IF(c >= 1, cnt1, 0)) as cnt1,
SUM(IF(c = 1, cnt1, 0)) as cnt2,
SUM(IF(c = 2, cnt1, 0)) as cnt3
from
oks.rdb_test1
group by
a;