为了更快的定位您的问题，请提供以下信息，谢谢
【详述】修改物化视图字段添加if操作后, 字段类型会变成varchar(65533) -> varchar(1048576) , 导致后续物化视图join报错 Cause an error: column schema not compatible: (name_a varchar(65533) NULL COMMENT “”) and (name_a varchar(1048576) NULL COMMENT “”).
【背景】修改底层物化视图字段添加IF操作
【业务影响】导致后续物化视图join报错, 需要重建才能可以激活
【是否存算分离】
【StarRocks版本】例如： 3.3.3-312ed45
【集群规模】
【联系方式】社区群10-楽y昂梁
复现代码:
– 建表 A\B
drop table if exists test_A ;
CREATE TABLE if not exists test_A (
id BIGINT NOT NULL,
name VARCHAR(65533)
)
PRIMARY KEY(id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
“replication_num” = “3”
);
drop table if exists test_B ;
CREATE TABLE if not exists test_B (
id BIGINT NOT NULL,
name VARCHAR(65533)
)
PRIMARY KEY(id)
DISTRIBUTED BY HASH(id)
PROPERTIES (
“replication_num” = “3”
);
– 物化视图 A . 开始创建时name字段不添加if操作
drop MATERIALIZED VIEW if exists test_A_mv ;
CREATE MATERIALIZED VIEW if not exists test_A_mv
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 MINUTE)
as
select
id,
name as name
from test_A
;
– 创建B物化视图, 引用A物化视图和B表.
drop MATERIALIZED VIEW if exists test_B_mv ;
CREATE MATERIALIZED VIEW if not exists test_B_mv
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 MINUTE) as
select
a.name as name_a,
b.name as name_b
from test_A_mv a
join test_B b on a.name = b.name
;
– 查看物化视图B的字段类型, 此时两个name都是varchar(65533)
DESCRIBE test_B_mv ;
DESCRIBE test_A_mv ;
– 查看物化视图状态.
SHOW MATERIALIZED VIEWS where name = ‘test_B_mv’
– 重建物化视图A. 对name字段添加IF操作
drop MATERIALIZED VIEW if exists test_A_mv ;
CREATE MATERIALIZED VIEW if not exists test_A_mv
DISTRIBUTED BY RANDOM
REFRESH ASYNC EVERY(INTERVAL 1 MINUTE)
as
select
id,
cast(if(name is not null, name ,’’) as varchar(65533)) name
from test_A
;
– 查看物化视图B状态.
SHOW MATERIALIZED VIEWS where name = ‘test_B_mv’ ;
– 重新激活
ALTER MATERIALIZED VIEW test_B_mv ACTIVE ;
– 报错 Cause an error: column schema not compatible: (name_a varchar(65533) NULL COMMENT “”) and (name_a varchar(1048576) NULL COMMENT “”).
– 此时需要重建物化视图B才能解决问题.
问: 物化视图可否显示指定数据类型. 而非推导类型. 或者有没有其他更好的办法解决这个问题 ?