为了更快的定位您的问题，请提供以下信息，谢谢
【详述】查询错误命中同步物化视图导致查询失败
【背景】做过哪些操作？
1、创建表和视图，插入数据
use test;
create table if not exists test.test_view_hit(
cate string,
dim1 string,
dim2 string,
cnt1 bigint default '0',
cnt2 bigint default '0'
)
DUPLICATE KEY(cate)
distributed by random
properties (
"replication_num" = "1"
);
create materialized view test_view_hit_mv as
select
cate,
sum(cnt1),
sum(cnt2)
from test_view_hit
group by cate
;
SHOW ALTER MATERIALIZED VIEW;
insert into test.test_view_hit values
('a', 'dim1', 'dim2', 10, 10), ('a', 'dim1', 'dim2', 10, 10), ('b', 'dim1', 'dim2', 10, 10)
;
2、查询
select
ARRAY_DISTINCT([dim1, dim2]) AS dim,
cnt1,
cnt2
from test_view_hit
where cate = 'c'
;
Empty set (0.01 sec)
with distinct_dim_combinations as (
select
ARRAY_DISTINCT([dim1, dim2]) AS dims,
cnt1,
cnt2
from test_view_hit
where cate = 'c'
)
select
items.unnest AS dim,
sum(cnt1),
sum(cnt2)
from distinct_dim_combinations, unnest(dims) AS items
group by 1
;
ERROR 1064 (HY000): invalid field name: dim1: BE:10002
3、查看查询计划
EXPLAIN with distinct_dim_combinations as (
select
ARRAY_DISTINCT([dim1, dim2]) AS dims,
cnt1,
cnt2
from test_view_hit
where cate = 'c'
)
select
items.unnest AS dim,
sum(cnt1),
sum(cnt2)
from distinct_dim_combinations, unnest(dims) AS items
group by 1
;
+---------------------------------------------------------------------------------+
| Explain String |
+---------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:9: unnest | 10: sum | 11: sum |
| PARTITION: HASH_PARTITIONED: 9: unnest |
| |
| RESULT SINK |
| |
| 5:AGGREGATE (merge finalize) |
| | output: sum(10: sum), sum(11: sum) |
| | group by: 9: unnest |
| | |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| colocate exec groups: ExecGroup{groupId=1, nodeIds=[0, 1, 2, 3]} |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| HASH_PARTITIONED: 9: unnest |
| |
| 3:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(6: mv_sum_cnt1), sum(7: mv_sum_cnt2) |
| | group by: 9: unnest |
| | |
| 2:TableValueFunction |
| | tableFunctionName: unnest |
| | columns: [unnest] |
| | returnTypes: [VARCHAR] |
| | |
| 1:Project |
| | <slot 6> : 6: mv_sum_cnt1 |
| | <slot 7> : 7: mv_sum_cnt2 |
| | <slot 8> : array_distinct([2: dim1,3: dim2]) |
| | |
| 0:OlapScanNode |
| TABLE: test_view_hit |
| PREAGGREGATION: OFF. Reason: Group columns isn't bound table test_view_hit |
| PREDICATES: DictDecode(12: cate, [<place-holder> = 'c']) |
| partitions=1/1 |
| rollup: test_view_hit_mv |
| tabletRatio=1/1 |
| tabletList=18394 |
| cardinality=2 |
| avgRowSize=12.0 |
+---------------------------------------------------------------------------------+
【业务影响】
查询报错，不应该命中物化视图
【是否存算分离】
否
【StarRocks版本】 3.5.6-3ba6b8d
【集群规模】单机
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】社区群25-吴邪
【附件】