SQL 错误 [1064] [42000]: Unknown error

sszc · 2023年04月10日 07:56

【详述】执行查询SQL时报：SQL 错误 [1064] [42000]: Unknown error
【StarRocks版本】2.5.3
【集群规模】3fe（1 follower+2observer）+8be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/512G/万兆
【附件】
建表：建表.sql (2.6 KB)
执行SQL：search.sql (6.4 KB)
profile: 不知道为什么，无法生成profile

LIANGCHAOHUA · 2023年04月10日 16:27

收到，我们先排查。

ZiheLiu · 2023年04月11日 05:32

辛苦发下报错时间段的 FE 日志内容 fe.warn.log

sszc · 2023年04月11日 07:49

当时fe.warn.log的日志：
OR aa.coupon_lt15_mem_cnt >= 1
OR aa.onln_address_order_mem_cnt >= 1
OR aa.onln_tel_order_mem_cnt >= 1
OR aa.pay_early_order_mem_cnt >= 1
OR (aa.order_mem_cnt >= 40 AND IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) >= 0.7)
OR aa.diff_order_id >= 5
OR aa.offln_pay_first_dlvr_order_cnt >= 1
OR aa.onln_pay_first_dlvr_order_cnt >= 1

java.lang.NullPointerException: null
2023-04-10 15:11:23,626 WARN (starrocks-mysql-nio-pool-20350|528362) [StmtExecutor.execute():530] execute Exception, sql /* ApplicationName=DataGrip 2020.1.5 / SELECT aa.dt
,aa.region_id
,aa.region_name
,aa.sub_region_id
,aa.sub_region_name
,aa.city_id
,aa.city_name
,aa.store_id
,aa.store_name
,aa.promo_id
,aa.promo_name
,aa.terminal_mem_cnt
,aa.terminal_order_mem_cnt
,CASE WHEN aa.terminal_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_terminal_abnormal
,aa.onln_terminal_order_mem_cnt
,CASE WHEN aa.onln_terminal_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_onln_terminal_abnormal
,aa.use_mem_cnt
,aa.use_client_cnt
,aa.coupon_diff_mem_cnt
,CASE WHEN aa.coupon_diff_mem_cnt >= 3 THEN 1 ELSE 0 END AS is_coupon_use_abnormal
,aa.coupon_lt15_mem_cnt
,aa.coupon_lt5_mem_cnt
,aa.coupon_5to10_mem_cnt
,aa.coupon_10to15_mem_cnt
,CASE WHEN aa.coupon_lt15_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_coupon_consumed_abnormal
,aa.onln_address_order_mem_cnt
,CASE WHEN aa.onln_address_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_onln_address_abnormal
,aa.onln_tel_order_mem_cnt
,CASE WHEN aa.onln_tel_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_onln_tel_abnormal
,aa.pay_early_order_mem_cnt
,CASE WHEN aa.pay_early_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_pay_early_abnormal
,aa.mem_cnt
,aa.order_mem_cnt
,IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) AS order_mem_rate
,CASE WHEN aa.order_mem_cnt >= 40 AND IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) >= 0.7 THEN 1 ELSE 0 END AS is_conv_abnormal
,aa.diff_order_id
,CASE WHEN aa.diff_order_id >= 5 THEN 1 ELSE 0 END AS is_diff_abnormal
–,aa.offln_pay_name
,aa.offln_pay_first_dlvr_order_cnt
,CASE WHEN aa.offln_pay_first_dlvr_order_cnt >= 1 THEN 1 ELSE 0 END is_offln_pay_abnormal
,aa.onln_pay_name
,aa.onln_pay_first_dlvr_order_cnt
,CASE WHEN aa.onln_pay_first_dlvr_order_cnt >= 1 THEN 1 ELSE 0 END is_onln_pay_abnormal
FROM (
SELECT a.dt
,MAX(b.region_id) AS region_id
,MAX(b.region_short_name) AS region_name
,MAX(b.sub_region_id) AS sub_region_id
,MAX(b.sub_region_name) AS sub_region_name
,MAX(b.city_id) AS city_id
,MAX(b.city_name) AS city_name
,b.business_store_id AS store_id
,MAX(b.store_name) AS store_name
,a.promo_id
,MAX(a.promo_name) AS promo_name
,COUNT(DISTINCT CASE WHEN a.terminal_udid IS NOT NULL AND a.terminal_mem_cnt >= 3 THEN a.mem_id ELSE NULL END) AS terminal_mem_cnt
,COUNT(DISTINCT CASE WHEN a.terminal_udid IS NOT NULL AND a.terminal_mem_cnt >= 3 THEN a.first_dlvr_order_id ELSE NULL END) AS terminal_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.onln_first_dlvr_udid IS NOT NULL AND a.onln_terminal_mem_cnt >= 3 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_terminal_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.use_client_id IS NOT NULL THEN a.mem_id ELSE NULL END) AS use_mem_cnt
,COUNT(DISTINCT a.use_client_id) AS use_client_cnt
,COUNT(DISTINCT CASE WHEN a.receive_client_id <> a.use_client_id THEN a.mem_id ELSE NULL END) AS coupon_diff_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed < 900 THEN a.mem_id ELSE NULL END) AS coupon_lt15_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed < 300 THEN a.mem_id ELSE NULL END) AS coupon_lt5_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed >= 300 AND a.receive_use_consumed < 600 THEN a.mem_id ELSE NULL END) AS coupon_5to10_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed >= 600 AND a.receive_use_consumed < 900 THEN a.mem_id ELSE NULL END) AS coupon_10to15_mem_cnt
,COUNT(DISTINCT CASE WHEN a.onln_first_dlvr_address IS NOT NULL AND a.onln_address_mem_cnt >= 3 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_address_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.onln_first_dlvr_tel IS NOT NULL AND a.onln_tel_mem_cnt >= 3 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_tel_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.first_pay_consumed < 0 THEN a.first_dlvr_order_id ELSE NULL END) AS pay_early_order_mem_cnt
,COUNT(DISTINCT a.mem_id) AS mem_cnt
,COUNT(DISTINCT a.first_dlvr_order_id) AS order_mem_cnt
,COUNT(DISTINCT CASE WHEN b.city_id <> c.city_id THEN a.first_dlvr_order_id ELSE NULL END) AS diff_order_id
–,MAX(CASE WHEN a.offln_pay_type IS NOT NULL AND a.offln_first_dlvr_order_cnt >= 40 AND a.offln_pay_first_dlvr_order_cnt / a.offln_first_dlvr_order_cnt >= 0.7 THEN a.offln_pay_name ELSE NULL END) AS offln_pay_name
,COUNT(DISTINCT CASE WHEN a.offln_pay_type IS NOT NULL AND a.offln_first_dlvr_order_cnt >= 40 AND a.offln_pay_first_dlvr_order_cnt / a.offln_first_dlvr_order_cnt >= 0.7 THEN a.offln_first_dlvr_order_id ELSE NULL END) AS offln_pay_first_dlvr_order_cnt
,MAX(DISTINCT CASE WHEN a.onln_pay_type IS NOT NULL AND a.onln_first_dlvr_order_cnt >= 40 AND a.onln_pay_first_dlvr_order_cnt / a.onln_first_dlvr_order_cnt >= 0.7 THEN a.onln_pay_name ELSE NULL END) AS onln_pay_name
,COUNT(DISTINCT CASE WHEN a.onln_pay_type IS NOT NULL AND a.onln_first_dlvr_order_cnt >= 40 AND a.onln_pay_first_dlvr_order_cnt / a.onln_first_dlvr_order_cnt >= 0.7 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_pay_first_dlvr_order_cnt
FROM ads.ads_bi_gp_mem_kpi_d_sr a
JOIN rpt.vi_dim_store_all b
ON a.promo_store_id = b.store_id
LEFT JOIN rpt.vi_dim_store_all c
ON a.first_dlvr_store_id = c.store_id
GROUP BY a.dt
,b.business_store_id
,a.promo_id
) aa
WHERE aa.terminal_mem_cnt >= 1
OR aa.onln_terminal_order_mem_cnt >= 1
OR aa.coupon_diff_mem_cnt >= 3
OR aa.coupon_lt15_mem_cnt >= 1
OR aa.onln_address_order_mem_cnt >= 1
OR aa.onln_tel_order_mem_cnt >= 1
OR aa.pay_early_order_mem_cnt >= 1
OR (aa.order_mem_cnt >= 40 AND IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) >= 0.7)
OR aa.diff_order_id >= 5
OR aa.offln_pay_first_dlvr_order_cnt >= 1
OR aa.onln_pay_first_dlvr_order_cnt >= 1
java.lang.NullPointerException: null
2023-04-10 15:13:00,415 WARN (starrocks-mysql-nio-pool-20345|528354) [StmtExecutor.execute():530] execute Exception, sql / ApplicationName=DataGrip 2020.1.5 */ SELECT aa.dt
,aa.region_id
,aa.region_name
,aa.sub_region_id
,aa.sub_region_name
,aa.city_id
,aa.city_name
,aa.store_id
,aa.store_name
,aa.promo_id
,aa.promo_name
,aa.terminal_mem_cnt
,aa.terminal_order_mem_cnt
,CASE WHEN aa.terminal_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_terminal_abnormal
,aa.onln_terminal_order_mem_cnt
,CASE WHEN aa.onln_terminal_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_onln_terminal_abnormal
,aa.use_mem_cnt
,aa.use_client_cnt
,aa.coupon_diff_mem_cnt
,CASE WHEN aa.coupon_diff_mem_cnt >= 3 THEN 1 ELSE 0 END AS is_coupon_use_abnormal
,aa.coupon_lt15_mem_cnt
,aa.coupon_lt5_mem_cnt
,aa.coupon_5to10_mem_cnt
,aa.coupon_10to15_mem_cnt
,CASE WHEN aa.coupon_lt15_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_coupon_consumed_abnormal
,aa.onln_address_order_mem_cnt
,CASE WHEN aa.onln_address_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_onln_address_abnormal
,aa.onln_tel_order_mem_cnt
,CASE WHEN aa.onln_tel_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_onln_tel_abnormal
,aa.pay_early_order_mem_cnt
,CASE WHEN aa.pay_early_order_mem_cnt >= 1 THEN 1 ELSE 0 END AS is_pay_early_abnormal
,aa.mem_cnt
,aa.order_mem_cnt
,IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) AS order_mem_rate
,CASE WHEN aa.order_mem_cnt >= 40 AND IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) >= 0.7 THEN 1 ELSE 0 END AS is_conv_abnormal
,aa.diff_order_id
,CASE WHEN aa.diff_order_id >= 5 THEN 1 ELSE 0 END AS is_diff_abnormal
–,aa.offln_pay_name
,aa.offln_pay_first_dlvr_order_cnt
,CASE WHEN aa.offln_pay_first_dlvr_order_cnt >= 1 THEN 1 ELSE 0 END is_offln_pay_abnormal
,aa.onln_pay_name
,aa.onln_pay_first_dlvr_order_cnt
,CASE WHEN aa.onln_pay_first_dlvr_order_cnt >= 1 THEN 1 ELSE 0 END is_onln_pay_abnormal
FROM (
SELECT a.dt
,MAX(b.region_id) AS region_id
,MAX(b.region_short_name) AS region_name
,MAX(b.sub_region_id) AS sub_region_id
,MAX(b.sub_region_name) AS sub_region_name
,MAX(b.city_id) AS city_id
,MAX(b.city_name) AS city_name
,b.business_store_id AS store_id
,MAX(b.store_name) AS store_name
,a.promo_id
,MAX(a.promo_name) AS promo_name
,COUNT(DISTINCT CASE WHEN a.terminal_udid IS NOT NULL AND a.terminal_mem_cnt >= 3 THEN a.mem_id ELSE NULL END) AS terminal_mem_cnt
,COUNT(DISTINCT CASE WHEN a.terminal_udid IS NOT NULL AND a.terminal_mem_cnt >= 3 THEN a.first_dlvr_order_id ELSE NULL END) AS terminal_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.onln_first_dlvr_udid IS NOT NULL AND a.onln_terminal_mem_cnt >= 3 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_terminal_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.use_client_id IS NOT NULL THEN a.mem_id ELSE NULL END) AS use_mem_cnt
,COUNT(DISTINCT a.use_client_id) AS use_client_cnt
,COUNT(DISTINCT CASE WHEN a.receive_client_id <> a.use_client_id THEN a.mem_id ELSE NULL END) AS coupon_diff_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed < 900 THEN a.mem_id ELSE NULL END) AS coupon_lt15_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed < 300 THEN a.mem_id ELSE NULL END) AS coupon_lt5_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed >= 300 AND a.receive_use_consumed < 600 THEN a.mem_id ELSE NULL END) AS coupon_5to10_mem_cnt
,COUNT(DISTINCT CASE WHEN a.receive_use_consumed >= 600 AND a.receive_use_consumed < 900 THEN a.mem_id ELSE NULL END) AS coupon_10to15_mem_cnt
,COUNT(DISTINCT CASE WHEN a.onln_first_dlvr_address IS NOT NULL AND a.onln_address_mem_cnt >= 3 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_address_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.onln_first_dlvr_tel IS NOT NULL AND a.onln_tel_mem_cnt >= 3 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_tel_order_mem_cnt
,COUNT(DISTINCT CASE WHEN a.first_pay_consumed < 0 THEN a.first_dlvr_order_id ELSE NULL END) AS pay_early_order_mem_cnt
,COUNT(DISTINCT a.mem_id) AS mem_cnt
,COUNT(DISTINCT a.first_dlvr_order_id) AS order_mem_cnt
,COUNT(DISTINCT CASE WHEN b.city_id <> c.city_id THEN a.first_dlvr_order_id ELSE NULL END) AS diff_order_id
–,MAX(CASE WHEN a.offln_pay_type IS NOT NULL AND a.offln_first_dlvr_order_cnt >= 40 AND a.offln_pay_first_dlvr_order_cnt / a.offln_first_dlvr_order_cnt >= 0.7 THEN a.offln_pay_name ELSE NULL END) AS offln_pay_name
,COUNT(DISTINCT CASE WHEN a.offln_pay_type IS NOT NULL AND a.offln_first_dlvr_order_cnt >= 40 AND a.offln_pay_first_dlvr_order_cnt / a.offln_first_dlvr_order_cnt >= 0.7 THEN a.offln_first_dlvr_order_id ELSE NULL END) AS offln_pay_first_dlvr_order_cnt
,MAX(DISTINCT CASE WHEN a.onln_pay_type IS NOT NULL AND a.onln_first_dlvr_order_cnt >= 40 AND a.onln_pay_first_dlvr_order_cnt / a.onln_first_dlvr_order_cnt >= 0.7 THEN a.onln_pay_name ELSE NULL END) AS onln_pay_name
,COUNT(DISTINCT CASE WHEN a.onln_pay_type IS NOT NULL AND a.onln_first_dlvr_order_cnt >= 40 AND a.onln_pay_first_dlvr_order_cnt / a.onln_first_dlvr_order_cnt >= 0.7 THEN a.onln_first_dlvr_order_id ELSE NULL END) AS onln_pay_first_dlvr_order_cnt
FROM ads.ads_bi_gp_mem_kpi_d_sr a
JOIN rpt.vi_dim_store_all b
ON a.promo_store_id = b.store_id
LEFT JOIN rpt.vi_dim_store_all c
ON a.first_dlvr_store_id = c.store_id
GROUP BY a.dt
,b.business_store_id
,a.promo_id
) aa
WHERE aa.terminal_mem_cnt >= 1
OR aa.onln_terminal_order_mem_cnt >= 1
OR aa.coupon_diff_mem_cnt >= 3
OR aa.coupon_lt15_mem_cnt >= 1
OR aa.onln_address_order_mem_cnt >= 1
OR aa.onln_tel_order_mem_cnt >= 1
OR aa.pay_early_order_mem_cnt >= 1
OR (aa.order_mem_cnt >= 40 AND IF(aa.mem_cnt = 0,0.0,aa.order_mem_cnt / aa.mem_cnt) >= 0.7)
OR aa.diff_order_id >= 5
OR aa.offln_pay_first_dlvr_order_cnt >= 1
OR aa.onln_pay_first_dlvr_order_cnt >= 1
java.lang.NullPointerException: null

ZiheLiu · 2023年04月15日 06:23

fe.warn.log 中，这一行下面若干行的内容是什么？再发得全一些。

另外，再辛苦发一个这个 SQL 的 query dump（生成 query dump 的文档见这里）。

ZiheLiu · 2023年04月17日 03:19

@U_1645753208786_6225 你好，方便加一下微信，再具体看下这个问题吗？微信号：liuzihe12

ZiheLiu · 2023年05月25日 02:23

你好，还在不？方便加一下微信，再具体看下这个问题吗？微信号：liuzihe12

U_1655783877940_3635 · 2023年06月13日 04:03

我这边有一个表之前正常的，突然也出现了这个错误。请问一下，您这个问题解决了吗？是如何解决的？谢谢