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() 中再进行多个字段的查询并排序时也不报错