【详述】查询报错 ERROR 1064 (HY000): Unknown error
【业务影响】
【StarRocks版本】1.19.3
【集群规模】2fe（1 follower+1observer）+4be
【机器信息】32/128G
【附件】
- 建表语句
[建表语句.txt|attachment](upload://myLjVPFPSjrIxdLpLvzkOlUYKKU.txt) (37.2 KB)
- 查询（报错）：
select pve_type AS pve_typeSPEVENT10021SPINDEX1,
pve_id AS pve_idSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10021' AND complete_type = '0' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX1,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10020' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10020SPINDEX2,
COUNT(DISTINCT (case when event_id = '10020' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10020SPINDEX2,
COUNT(case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX3,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX3
FROM bdc_dwd.dwd_gamelog_1065_inc_1d
WHERE platform = 'SERVER'
AND event_id in ('10021', '10020')
AND dt >= '2021-12-01'
AND dt <= '2021-12-09'
GROUP BY pve_type, pve_id
LIMIT 2000
- 查询：缩小查询分区数量不报错或者只留一个count(distinct) 也不报错
select pve_type AS pve_typeSPEVENT10021SPINDEX1,
pve_id AS pve_idSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10021' AND complete_type = '0' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX1,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10020' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10020SPINDEX2,
COUNT(DISTINCT (case when event_id = '10020' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10020SPINDEX2,
COUNT(case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX3,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX3
FROM bdc_dwd.dwd_gamelog_1065_inc_1d
WHERE platform = 'SERVER'
AND event_id in ('10021', '10020')
AND dt >= '2021-12-08'
AND dt <= '2021-12-09'
GROUP BY pve_type, pve_id
LIMIT 2000
解决方案：1.19+ 默认开启CBO，设置sql关闭CBO
select /*+ SET_VAR(enable_cbo = false) */ pve_type AS pve_typeSPEVENT10021SPINDEX1,
pve_id AS pve_idSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10021' AND complete_type = '0' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX1,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10020' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10020SPINDEX2,
COUNT(DISTINCT (case when event_id = '10020' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10020SPINDEX2,
COUNT(case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX3,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX3
FROM bdc_dwd.dwd_gamelog_1065_inc_1d
WHERE platform = 'SERVER'
AND event_id in ('10021', '10020')
AND dt >= '2021-12-01'
AND dt <= '2021-12-09'
GROUP BY pve_type, pve_id
LIMIT 2000
WARN日志：
2021-12-30 14:02:51,092 WARN (starrocks-mysql-nio-pool-5367|261514) [StmtExecutor.execute():456] execute Exception, sql select pve_type AS pve_typeSPEVENT10021SPINDEX1,
pve_id AS pve_idSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10021' AND complete_type = '0' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX1,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX1,
COUNT(case when event_id = '10020' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10020SPINDEX2,
COUNT(DISTINCT (case when event_id = '10020' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10020SPINDEX2,
COUNT(case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end) AS role_keySPCOLCOUNTSPEVENT10021SPINDEX3,
COUNT(DISTINCT (case when event_id = '10021' AND complete_type = '0' AND iswin = '1' then role_key else null end)) AS role_keySPCOLCOUNT_DISTINCTSPEVENT10021SPINDEX3
FROM bdc_dwd.dwd_gamelog_1065_inc_1d
WHERE platform = 'SERVER'
AND event_id in ('10021', '10020')
AND dt >= '2021-12-01'
AND dt <= '2021-12-09'
GROUP BY pve_type, pve_id
LIMIT 2000
java.lang.NullPointerException: null
1赞
你好，请问下是关闭cbo后可以正常运行？如果是的话，辛苦贴下建表语句，谢谢