执行sql导致be全部挂掉

💬 StarRocks 用户问答
#1

【详述】执行sql，导致be全部挂掉
【背景】执行sql
【业务影响】
【StarRocks版本】2.3.2
【集群规模】例如：3fe+3ob+10be
【机器信息】48C+128G
【附件】
执行sql：
insert into rpt.rpt_recommend_daily
select ‘2022-10-24’
,t1.city_name as city_name
,register_count
,first_log_cnt
,call_count
,call_count_user
,complete_users
,complete_orders
,succ_pay_today_comp_orders
,complete_order_30d

from
(select ifnull(reg_city_name,‘全国’) as city_name
,count(distinct case when substr(promotor_time,1,10)=‘2022-10-24’ then passenger_id else null end) as register_count
,count(distinct case when substr(first_login_app_time,1,10)=‘2022-10-24’ then passenger_id else null end) as first_log_cnt
from tmp.rpt_recommend_daily_03
group by grouping sets((),(reg_city_name))
)t1
left join
(
select ifnull(city_name,‘全国’) as city_name
,sum(b.today_call_orders) as call_count
from
(
select * from pdm.pdm_t01_user_promotor where product_line = 1 and promotor_type=‘CTC’)a
left join
(
select passenger_id,city_name,
sum(case when date =‘2022-10-24’ then call_orders else 0 end) as today_call_orders,
sum(case when date =‘2022-10-24’ then complete_orders else 0 end) as today_complete_orders,
sum(case when date =‘2022-10-24’ then actual_pay else 0 end) as actual_pay,
sum(complete_orders) as thirty_complete_orders
from dm.dm_trip_passenger_static_daily
where date >= date_sub(‘2022-10-24’,30)
and date <=‘2022-10-24’
and (call_orders >0 or complete_orders>0 or actual_pay>0)
and third_platform_desc in (‘微信小程序’,‘支付宝小程序’)
group by passenger_id,city_name
)b
on a.user_id = b.passenger_id
group by grouping sets((),(ifnull(city_name,‘全国’)))
) t4
on t1.city_name = t4.city_name

#2

如果把加粗部分改为 group by grouping sets((),(city_name))，去掉ifnull的判断，执行就没问题

#3

发一下 be/log/be.out 日志看下呢

#4

执行计划
PLAN FRAGMENT 0
OUTPUT EXPRS:173: date | 17: ifnull | 174: pv | 175: uv | 176: passenger_share_count | 177: register_count | 178: first_pay_count | 179: first_log_cnt | 180: ention_count | 181: bubble_count | 182: call_counts | 183: call_users | 184: complete_users | 185: complete_orders | 186: succ_pay_today_comp_orders | 187: app_total_login | 188: first_call_cnt | 189: first_complete_cnt | 190: first_complete_order_7d_reg | 191: first_complete_order_7d | 192: complete_order_30d
PARTITION: HASH_PARTITIONED: 153: ifnull

OLAP TABLE SINK
TUPLE ID: 18
RANDOM

26:Project
| <slot 17> : 17: ifnull
| <slot 173> : CAST(‘2022-10-10’ AS DATE)
| <slot 174> : 193: cast
| <slot 175> : CAST(0 AS INT)
| <slot 176> : CAST(0 AS INT)
| <slot 177> : CAST(14: count AS INT)
| <slot 178> : CAST(0 AS INT)
| <slot 179> : CAST(15: count AS INT)
| <slot 180> : CAST(0 AS INT)
| <slot 181> : CAST(0 AS INT)
| <slot 182> : CAST(154: sum AS INT)
| <slot 183> : CAST(0 AS INT)
| <slot 184> : CAST(0 AS INT)
| <slot 185> : CAST(0 AS INT)
| <slot 186> : CAST(0 AS INT)
| <slot 187> : CAST(0 AS INT)
| <slot 188> : CAST(0 AS INT)
| <slot 189> : CAST(0 AS INT)
| <slot 190> : CAST(0 AS INT)
| <slot 191> : CAST(0 AS INT)
| <slot 192> : CAST(0 AS INT)
| common expressions:
| <slot 193> : CAST(0 AS INT)
|
25:HASH JOIN
| join op: RIGHT OUTER JOIN (PARTITIONED)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 153: ifnull = 17: ifnull
|
|----24:EXCHANGE
|
16:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 2: reg_city_name, 16: GROUPING_ID

STREAM DATA SINK
EXCHANGE ID: 24
HASH_PARTITIONED: 17: ifnull

23:Project
| <slot 14> : 14: count
| <slot 15> : 15: count
| <slot 17> : ifnull(2: reg_city_name, ‘全国’)
|
22:AGGREGATE (merge finalize)
| output: multi_distinct_count(14: count), multi_distinct_count(15: count)
| group by: 2: reg_city_name, 16: GROUPING_ID
|
21:EXCHANGE

PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 21
HASH_PARTITIONED: 2: reg_city_name, 16: GROUPING_ID

20:AGGREGATE (update serialize)
| STREAMING
| output: multi_distinct_count(12: case), multi_distinct_count(13: case)
| group by: 2: reg_city_name, 16: GROUPING_ID
|
19:REPEAT_NODE
| repeat: repeat 1 lines [[], [2]]
|
18:Project
| <slot 2> : 2: reg_city_name
| <slot 12> : if(substr(CAST(10: promotor_time AS VARCHAR), 1, 10) = ‘2022-10-24’, 1: passenger_id, NULL)
| <slot 13> : if(substr(CAST(5: first_login_app_time AS VARCHAR), 1, 10) = ‘2022-10-24’, 1: passenger_id, NULL)
|
17:OlapScanNode
TABLE: rpt_recommend_daily_03
PREAGGREGATION: ON
partitions=1/1
rollup: rpt_recommend_daily_03
tabletRatio=10/10
tabletList=25562669,25562673,25562677,25562681,25562685,25562689,25562693,25562697,25562701,25562705
cardinality=2222342
avgRowSize=53.999725
numNodes=0

PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 153: ifnull, 155: GROUPING_ID

STREAM DATA SINK
EXCHANGE ID: 16
HASH_PARTITIONED: 153: ifnull

15:Project
| <slot 153> : 153: ifnull
| <slot 154> : 154: sum
|
14:AGGREGATE (merge finalize)
| output: sum(154: sum)
| group by: 153: ifnull, 155: GROUPING_ID
|
13:EXCHANGE

PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 42: passenger_id

STREAM DATA SINK
EXCHANGE ID: 13
HASH_PARTITIONED: 153: ifnull, 155: GROUPING_ID

12:AGGREGATE (update serialize)
| STREAMING
| output: sum(149: sum)
| group by: 153: ifnull, 155: GROUPING_ID
|
11:REPEAT_NODE
| repeat: repeat 1 lines [[], [153]]
|
10:Project
| <slot 149> : 149: sum
| <slot 153> : ifnull(46: city_name, ‘全国’)
|
9:HASH JOIN
| join op: RIGHT OUTER JOIN (PARTITIONED)
| hash predicates:
| colocate: false, reason:
| equal join conjunct: 42: passenger_id = 20: user_id
|
|----8:EXCHANGE
|
5:EXCHANGE

PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 08
HASH_PARTITIONED: 20: user_id

7:Project
| <slot 20> : 20: user_id
|
6:OlapScanNode
TABLE: pdm_t01_user_promotor
PREAGGREGATION: ON
PREDICATES: 21: product_line = 1, 19: promotor_type = ‘CTC’
partitions=1/1
rollup: pdm_t01_user_promotor
tabletRatio=10/10
tabletList=26159014,26159018,26159022,26159026,26159030,26159034,26159038,26159042,26159046,26159050
cardinality=692553
avgRowSize=36.999996
numNodes=0

PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 42: passenger_id, 46: city_name

STREAM DATA SINK
EXCHANGE ID: 05
HASH_PARTITIONED: 42: passenger_id

4:AGGREGATE (merge finalize)
| output: sum(149: sum)
| group by: 42: passenger_id, 46: city_name
|
3:EXCHANGE

PLAN FRAGMENT 7
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 03
HASH_PARTITIONED: 42: passenger_id, 46: city_name

2:AGGREGATE (update serialize)
| STREAMING
| output: sum(146: case)
| group by: 42: passenger_id, 46: city_name
|
1:Project
| <slot 42> : 42: passenger_id
| <slot 46> : 46: city_name
| <slot 146> : if(41: date = ‘2022-10-24’, 50: call_orders, 0)
|
0:OlapScanNode
TABLE: dm_trip_passenger_static_daily
PREAGGREGATION: ON
PREDICATES: 41: date <= ‘2022-10-24’, ((50: call_orders > 0) OR (52: complete_orders > 0)) OR (134: actual_pay > 0), 91: third_platform_desc IN (‘微信小程序’, ‘支付宝小程序’)
partitions=31/1534
rollup: dm_trip_passenger_static_daily
tabletRatio=310/310
tabletList=17191064,17191068,17191072,17191076,17191080,17191084,17191088,17191092,17191096,17191100 …
cardinality=112820121
avgRowSize=62.850697
numNodes=0

#5

已定位到原因，我们会尽快修复

京ICP备2022026421号-1