查询异步物化视图时排序报错并使集群down机

Viewed 70

selectDB-2.1.2版本,在查询物化视图时排序报错

物化视图创建语句(脱敏后):

create materialized view test
bulid deferred refresh compelete on schedule every 1 minute starts "yyyy-MM-dd"
distributed by hash(BID) BUCKETS 3
PROPERTIES
('replication_num' = '3'
,'store_row_column' = 'true'
)
as
SELECT
, A.BID
, D.PES
, F.IW
, D.LS
, D.FT
, B.PT
, A.BN
, B.PP
, H.CT
, D.CN AS D_CN
, G.CA,
, G.PCN
, G.OCN
, L.CN AS L_CN
, J.CN AS J_CN
, A.FA
, A.AFNA
, A.AT
, A.LD
, B.ED
, A.AFR
, A.AFF
, A.AFT
, K.CN AS K_CN 
, G.LEVELS
, A.AS aS ONE_STATUS
, C.AS aS TWO_STATUS
, IF(D.FT = 2,F.AT, NULL)AS AT
FROM A
LEFT B ON A.ID=B.FID
LEFT JOIN  C ON A.BID= C.BID AND C.BN=20 and C.I_D=0
LEFT JOIN D ON A.BID=D.PID AND D.STATUS=1
LEFT JOIN E ON E.PID = A.BID
LEFT JOIN F ON F.ID = E.IPID
LRFT JOIN DIM_COMPANY G ON D.CN = G.CN
LRFT JOIN DIM_COMPANY H ON H.id = A.ACID
LEFT JOIN DIM_COMPANY J ON J.id = G.IID
LEFT JOIN DIM_COMPANY K ON K.id = G.SID
LEFT JOIN DIM_COMPANY L ON L.id = G.LSID
WHERE A.AS=60
AND A.BN=10 and A.I_D=0
AND ((D.FT In(3,6) and C.AS =68 ) Or (D.FT not in(3,6) and (C.AS iS null or C.AS in(0,10,20,50,60,80,90,110,120))))

问题描述:

当对此物化视图查询多个字段并排序时报错并使集群down机,报错信息如下:

当查询少数字段时并不报错
当将order by 的排序字段放进 distributed by hash() 中再进行多个字段的查询并排序时也不报错

2 Answers
  1. 是直查物化视图报错,还是透明改写时报错?
  2. 贴一下报错时,be/log/be.out中的栈信息
  3. 只提供创建物化视图的语句无法复现分析此问题。至少需要物化视图依赖的基表建表语句。另外,此种错误大概率和数据相关,所以可能还需要相关数据才可。

哈喽,方便的话可以加下我主页微信,我们一起看下这个问题的,后续会把结论同步到帖子下