starrocks join返回结果行数不固定

yads · 2024年07月22日 05:57

为了更快的定位您的问题，请提供以下信息，谢谢
【详述】starrocks inner join返回结果行数不固定（左右两张表数据无变化）
【背景】使用了inner join
【业务影响】返回结果行数不固定
【是否存算分离】否
【StarRocks版本】例如：3.1.13
【集群规模】例如：3fe（1 follower+2observer）+3be
【机器信息】CPU虚拟核/内存/网卡，64C/256G/万兆
【附件】

-查询SQL（c1的结果集与b的结果集inner join）
select
c1.customerCategoryId,
c1.customerCategoryName,
c1.customerId,
c1.customerName,
c1.bizManagerName,
c1.bizManagerWxUserId,
c1.saleAreaName,
c1.priceLevelName,
c1.hasCategory,
c1.customerCode,
c1.firstOrderTime,
if( b.BillType = 9,
b.BillNo,
null ) billNum,
(case
when b.BillType = 6 then - b.qty
when b.ActivityType = 0 then b.qty
else 0
end) * b.UnitRate qty,
IF(b.BillType = 6,
-b.AfterSpecialDiscountMoney,
b.AfterSpecialDiscountMoney) money,
IF(b.BillType = 6,
-b.subTotal,
b.subTotal ) subTotal,
IF(b.BillType = 6,
-b.TaxAfterSpecialDiscountMoney,
b.TaxAfterSpecialDiscountMoney ) discountAfterTaxTotal,
IF(b.BillType = 6,
-b.discountAfterTotal,
b.discountAfterTotal ) discountAfterTotal,
IF(b.BillType = 6,
-b.taxMoney,
b.taxMoney ) taxMoney,
if(b.ActivityType = 0,
IF(b.BillType = 6,
-b.CostSubTotal,
b.CostSubTotal),
0) saleCost,
if(b.ActivityType not in (0, 3), IF(b.BillType = 6,
-b.CostSubTotal,
b.CostSubTotal),
0) giveCost,
if(b.BillType = 9
and b.ActivityType not in (0, 3), b.qty * b.UnitRate , 0) giveQty, if(b.BillType = 9,
b.DeliveryQty * b.UnitRate,
0 ) deliveryQty,
IF(b.BillType = 6,
-b.discountMoney,
b.discountMoney ) discountMoney,
if(b.BillType = 6,
0,
b.DiscountAfterTotal - b.AfterSpecialDiscountMoney) saleDiscount
from
(
SELECT
c.BusinessId CustomerId,
c.NAME CustomerName,
array_join (array_sortby(array_agg(bi.Name),
array_agg(bi.BusinessId)),
“,” ) bizManagerName,
array_join (array_sortby(array_agg(bi.WxUserId),
array_agg(bi.BusinessId)),
“,” ) bizManagerWxUserId,
cr.saleAreaName saleAreaName,
c.TypeName customerCategoryName,
c.TypeId customerCategoryId,
pl.Name priceLevelName,
0 hasCategory,
c.SerialNo CustomerCode,
c.firstOrderTime
FROM
dim.statistics_customer_baseinfo c
left join ods_goods.price_level pl on
c.PriceLevelId = pl.BusinessId
and pl.CorpId = 150011481
and pl.DataStatus = 1
left JOIN ods_customer.statistics_customer_superior bi ON
bi.CustomerId = c.BusinessId
AND bi.CorpId = 150011481
and bi.DataStatus = 1
and bi.PartitionId = mod(150011481,
3)
JOIN (
select
array_join (array_sortby(array_agg(cr.NAME),
array_agg(cr.Level )),
“,” ) saleAreaName,
cr.CustomerId,
cr.OptionId
FROM
ods_customer.customer_region cr
where
cr.CorpId = 150011481
AND cr.DataStatus = 1
and cr.PartitionId = mod(150011481,
5)
and cr.OptionId in (440521540749078528)
group by
cr.CustomerId ) cr ON
cr.CustomerId = c.BusinessId
WHERE
c.CorpId = 150011481
and c.DataStatus = 1
and c.CustomerCategory in (2, 3)
and c.PartitionId = mod(150011481,
5)
GROUP BY
c.BusinessId ) c1
inner JOIN (
select
b.SkuId,
b.qty,
b.ActivityType,
b.AfterSpecialDiscountMoney,
b.DiscountAfterTotal,
b.subTotal,
b.discountAfterTaxTotal,
b.TaxAfterSpecialDiscountMoney,
b.taxMoney,
b.CostSubTotal,
b.UnitRate,
b.DeliveryQty,
b.WarehouseId,
b.DataStatus,
b.discountMoney ,
b2.CustomerId CustomerId,
b2.BillType,
b2.BillNo,
b2.handlerId,
b.SpuId
from
dwd.sale_bill_detail b
INNER JOIN dwd.bill b2 ON
b2.BusinessId = b.BillId
and b2.CorpId = b.CorpId
and b2.PartitionId = mod(150011481,
6)
INNER JOIN dim.goods_sku g on
g.BusinessId = b.SkuId
and g.SpuId = b.SpuId
and g.corpId = 150011481
and g.PartitionId = mod(150011481,
6)
where
b.corpId = 150011481
AND b.ActivityType != 3
and b.PartitionId = mod(150011481,
6)
AND b2.corpId = 150011481
AND b2.DataStatus = 1
AND b.DataStatus = 1
and b2.Status = 1
and b2.BillType in (9, 6)
AND b2.MakeTime >= 1721404800000
AND b2.MakeTime <= 1721491199999
and g.datastatus = 1 ) b on
c1.CustomerId = b.CustomerId
-未加shuffle关键字的查询计划：
PLAN FRAGMENT 0
OUTPUT EXPRS:114: any_value | 113: any_value | 3: BusinessId | 111: any_value | 118: array_join | 119: array_join | 112: any_value | 115: any_value | 120: expr | 116: any_value | 117: any_value | 496: if | 497: expr | 498: if | 499: if | 500: if | 501: if | 502: if | 503: if | 504: if | 505: if | 506: if | 507: if | 508: if
PARTITION: UNPARTITIONED

RESULT SINK

39:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 39
UNPARTITIONED

PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 124: BillId, 122: CorpId

STREAM DATA SINK
EXCHANGE ID: 36
BUCKET_SHUFFLE_HASH_PARTITIONED: 245: CustomerId

PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 33
HASH_PARTITIONED: 212: BusinessId, 211: CorpId

32:Project
| <slot 211> : 211: CorpId
| <slot 212> : 212: BusinessId
| <slot 213> : 213: BillType
| <slot 214> : 214: BillNo
| <slot 245> : 245: CustomerId
|
31:OlapScanNode
TABLE: bill
PREAGGREGATION: ON
PREDICATES: 245: CustomerId IS NOT NULL, 211: CorpId = 150011481, 337: DataStatus = 1, 222: Status = 1, 213: BillType IN (9, 6), 219: MakeTime >= 1721404800000, 219: MakeTime <= 1721491199999
partitions=1/6
rollup: bill
tabletRatio=6/6
tabletList=271299,271303,271307,271311,271315,271319
cardinality=1
avgRowSize=58.037296
numNodes=0

PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 30
HASH_PARTITIONED: 124: BillId, 122: CorpId

PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 27
BUCKET_SHUFFLE_HASH_PARTITIONED: 128: SkuId

26:Project
| <slot 122> : 122: CorpId
| <slot 124> : 124: BillId
| <slot 127> : 127: SpuId
| <slot 128> : 128: SkuId
| <slot 134> : 134: UnitRate
| <slot 135> : 135: Qty
| <slot 138> : 138: SubTotal
| <slot 141> : 141: DiscountAfterTotal
| <slot 142> : 142: DiscountMoney
| <slot 146> : 146: TaxMoney
| <slot 148> : 148: CostSubTotal
| <slot 151> : 151: DeliveryQty
| <slot 158> : 158: ActivityType
| <slot 163> : 163: AfterSpecialDiscountMoney
| <slot 165> : 165: TaxAfterSpecialDiscountMoney
|
25:OlapScanNode
TABLE: sale_bill_detail
PREAGGREGATION: ON
PREDICATES: 122: CorpId = 150011481, 158: ActivityType != 3, 199: DataStatus = 1, 128: SkuId IS NOT NULL, 127: SpuId IS NOT NULL
partitions=1/6
rollup: sale_bill_detail
tabletRatio=6/6
tabletList=119644,119648,119652,119656,119660,119664
cardinality=1
avgRowSize=145.0
numNodes=0

PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 95: CustomerId

STREAM DATA SINK
EXCHANGE ID: 18
UNPARTITIONED

PLAN FRAGMENT 7
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 15
HASH_PARTITIONED: 95: CustomerId

14:AGGREGATE (update serialize)
| STREAMING
| output: array_agg(101: Name), array_agg(103: Level)
| group by: 95: CustomerId
|
13:Project
| <slot 95> : 95: CustomerId
| <slot 101> : 101: Name
| <slot 103> : 103: Level
|
12:OlapScanNode
TABLE: customer_region
PREAGGREGATION: ON
PREDICATES: 92: CorpId = 150011481, 99: DataStatus = 1, 96: OptionId = 440521540749078528
partitions=1/5
rollup: customer_region
tabletRatio=6/6
tabletList=216606,216610,216614,216618,216622,216626
cardinality=1
avgRowSize=30.05265
numNodes=0

PLAN FRAGMENT 8
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 09
BUCKET_SHUFFLE_HASH_PARTITIONED: 81: CustomerId

8:Project
| <slot 79> : 79: BusinessId
| <slot 81> : 81: CustomerId
| <slot 88> : 88: Name
| <slot 90> : 90: WxUserId
|
7:OlapScanNode
TABLE: statistics_customer_superior
PREAGGREGATION: ON
PREDICATES: 78: CorpId = 150011481, 86: DataStatus = 1
partitions=1/3
rollup: statistics_customer_superior
tabletRatio=6/6
tabletList=216436,216440,216444,216448,216452,216456
cardinality=89
avgRowSize=30.079765
numNodes=0

PLAN FRAGMENT 9
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 04
UNPARTITIONED

3:Project
| <slot 61> : 61: BusinessId
| <slot 63> : 63: Name
|
2:OlapScanNode
TABLE: price_level
PREAGGREGATION: ON
PREDICATES: 60: CorpId = 150011481, 74: DataStatus = 1
partitions=3/3
rollup: price_level
tabletRatio=18/18
tabletList=131538,131542,131546,131550,131554,131558,131562,131566,131570,131574 …
cardinality=2
avgRowSize=24.226665
numNodes=0

-加上shuffle关键字的查询计划：
PLAN FRAGMENT 0
OUTPUT EXPRS:114: any_value | 113: any_value | 3: BusinessId | 111: any_value | 118: array_join | 119: array_join | 112: any_value | 115: any_value | 120: expr | 116: any_value | 117: any_value | 496: if | 497: expr | 498: if | 499: if | 500: if | 501: if | 502: if | 503: if | 504: if | 505: if | 506: if | 507: if | 508: if
PARTITION: UNPARTITIONED

RESULT SINK

43:EXCHANGE

PLAN FRAGMENT 1
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 3: BusinessId

STREAM DATA SINK
EXCHANGE ID: 43
UNPARTITIONED

42:Project
| <slot 3> : 3: BusinessId
| <slot 111> : 111: any_value
| <slot 112> : 112: any_value
| <slot 113> : 113: any_value
| <slot 114> : 114: any_value
| <slot 115> : 115: any_value
| <slot 116> : 116: any_value
| <slot 117> : 117: any_value
| <slot 118> : 118: array_join
| <slot 119> : 119: array_join
| <slot 120> : 120: expr
| <slot 496> : if(512: expr, 214: BillNo, NULL)
| <slot 497> : CASE WHEN 509: expr THEN -1 * 135: Qty WHEN 510: expr THEN 135: Qty ELSE 0 END * 134: UnitRate
| <slot 498> : if(509: expr, -1 * 163: AfterSpecialDiscountMoney, 163: AfterSpecialDiscountMoney)
| <slot 499> : if(509: expr, -1 * 138: SubTotal, 138: SubTotal)
| <slot 500> : if(509: expr, -1 * 165: TaxAfterSpecialDiscountMoney, 165: TaxAfterSpecialDiscountMoney)
| <slot 501> : if(509: expr, -1 * 141: DiscountAfterTotal, 141: DiscountAfterTotal)
| <slot 502> : if(509: expr, -1 * 146: TaxMoney, 146: TaxMoney)
| <slot 503> : if(510: expr, 514: if, 0)
| <slot 504> : if(513: expr, 514: if, 0)
| <slot 505> : if((512: expr) AND (513: expr), 135: Qty * 134: UnitRate, 0)
| <slot 506> : if(512: expr, 151: DeliveryQty * 134: UnitRate, 0)
| <slot 507> : if(509: expr, -1 * 142: DiscountMoney, 142: DiscountMoney)
| <slot 508> : if(509: expr, 0, 141: DiscountAfterTotal - 163: AfterSpecialDiscountMoney)
| common expressions:
| <slot 512> : 213: BillType = 9
| <slot 513> : 158: ActivityType NOT IN (0, 3)
| <slot 514> : if(509: expr, 511: multiply, 148: CostSubTotal)
| <slot 509> : 213: BillType = 6
| <slot 510> : 158: ActivityType = 0
| <slot 511> : -1 * 148: CostSubTotal
|
41:HASH JOIN
| join op: INNER JOIN (BUCKET_SHUFFLE(S))
| colocate: false, reason:
| equal join conjunct: 3: BusinessId = 245: CustomerId
|
|----40:EXCHANGE
|
26:Project
| <slot 3> : 3: BusinessId
| <slot 111> : 111: any_value
| <slot 112> : 112: any_value
| <slot 113> : 113: any_value
| <slot 114> : 114: any_value
| <slot 115> : 115: any_value
| <slot 116> : 116: any_value
| <slot 117> : 117: any_value
| <slot 118> : array_join(array_sortby(108: array_agg, 109: array_agg), ‘,’)
| <slot 119> : array_join(array_sortby(110: array_agg, 109: array_agg), ‘,’)
| <slot 120> : 0
|
25:AGGREGATE (merge finalize)
| output: any_value(113: any_value), any_value(114: any_value), any_value(115: any_value), any_value(116: any_value), any_value(117: any_value), array_agg(108: array_agg), array_agg(109: array_agg), array_agg(110: array_agg), any_value(111: any_value), any_value(112: any_value)
| group by: 3: BusinessId
|
24:EXCHANGE

PLAN FRAGMENT 2
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 124: BillId, 122: CorpId

STREAM DATA SINK
EXCHANGE ID: 40
HASH_PARTITIONED: 245: CustomerId

PLAN FRAGMENT 3
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 37
HASH_PARTITIONED: 212: BusinessId, 211: CorpId

36:Project
| <slot 211> : 211: CorpId
| <slot 212> : 212: BusinessId
| <slot 213> : 213: BillType
| <slot 214> : 214: BillNo
| <slot 245> : 245: CustomerId
|
35:OlapScanNode
TABLE: bill
PREAGGREGATION: ON
PREDICATES: 245: CustomerId IS NOT NULL, 211: CorpId = 150011481, 337: DataStatus = 1, 222: Status = 1, 213: BillType IN (9, 6), 219: MakeTime >= 1721404800000, 219: MakeTime <= 1721491199999
partitions=1/6
rollup: bill
tabletRatio=6/6
tabletList=271299,271303,271307,271311,271315,271319
cardinality=1
avgRowSize=58.037296
numNodes=0

PLAN FRAGMENT 4
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 34
HASH_PARTITIONED: 124: BillId, 122: CorpId

PLAN FRAGMENT 5
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 31
BUCKET_SHUFFLE_HASH_PARTITIONED: 128: SkuId

30:Project
| <slot 122> : 122: CorpId
| <slot 124> : 124: BillId
| <slot 127> : 127: SpuId
| <slot 128> : 128: SkuId
| <slot 134> : 134: UnitRate
| <slot 135> : 135: Qty
| <slot 138> : 138: SubTotal
| <slot 141> : 141: DiscountAfterTotal
| <slot 142> : 142: DiscountMoney
| <slot 146> : 146: TaxMoney
| <slot 148> : 148: CostSubTotal
| <slot 151> : 151: DeliveryQty
| <slot 158> : 158: ActivityType
| <slot 163> : 163: AfterSpecialDiscountMoney
| <slot 165> : 165: TaxAfterSpecialDiscountMoney
|
29:OlapScanNode
TABLE: sale_bill_detail
PREAGGREGATION: ON
PREDICATES: 122: CorpId = 150011481, 158: ActivityType != 3, 199: DataStatus = 1, 128: SkuId IS NOT NULL, 127: SpuId IS NOT NULL
partitions=1/6
rollup: sale_bill_detail
tabletRatio=6/6
tabletList=119644,119648,119652,119656,119660,119664
cardinality=1
avgRowSize=145.0
numNodes=0

PLAN FRAGMENT 6
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 24: PriceLevelId

STREAM DATA SINK
EXCHANGE ID: 24
HASH_PARTITIONED: 3: BusinessId

PLAN FRAGMENT 7
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 20
HASH_PARTITIONED: 61: BusinessId

19:Project
| <slot 61> : 61: BusinessId
| <slot 63> : 63: Name
|
18:OlapScanNode
TABLE: price_level
PREAGGREGATION: ON
PREDICATES: 60: CorpId = 150011481, 74: DataStatus = 1
partitions=3/3
rollup: price_level
tabletRatio=18/18
tabletList=131538,131542,131546,131550,131554,131558,131562,131566,131570,131574 …
cardinality=2
avgRowSize=24.226665
numNodes=0

PLAN FRAGMENT 8
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 81: CustomerId

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

PLAN FRAGMENT 9
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 14
HASH_PARTITIONED: 3: BusinessId

PLAN FRAGMENT 10
OUTPUT EXPRS:
PARTITION: HASH_PARTITIONED: 95: CustomerId

STREAM DATA SINK
EXCHANGE ID: 11
BUCKET_SHUFFLE_HASH_PARTITIONED: 95: CustomerId

PLAN FRAGMENT 11
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 08
HASH_PARTITIONED: 95: CustomerId

7:AGGREGATE (update serialize)
| STREAMING
| output: array_agg(101: Name), array_agg(103: Level)
| group by: 95: CustomerId
|
6:Project
| <slot 95> : 95: CustomerId
| <slot 101> : 101: Name
| <slot 103> : 103: Level
|
5:OlapScanNode
TABLE: customer_region
PREAGGREGATION: ON
PREDICATES: 92: CorpId = 150011481, 99: DataStatus = 1, 96: OptionId = 440521540749078528
partitions=1/5
rollup: customer_region
tabletRatio=6/6
tabletList=216606,216610,216614,216618,216622,216626
cardinality=1
avgRowSize=30.05265
numNodes=0

PLAN FRAGMENT 12
OUTPUT EXPRS:
PARTITION: RANDOM

STREAM DATA SINK
EXCHANGE ID: 02
HASH_PARTITIONED: 81: CustomerId

1:Project
| <slot 79> : 79: BusinessId
| <slot 81> : 81: CustomerId
| <slot 88> : 88: Name
| <slot 90> : 90: WxUserId
|
0:OlapScanNode
TABLE: statistics_customer_superior
PREAGGREGATION: ON
PREDICATES: 78: CorpId = 150011481, 86: DataStatus = 1
partitions=1/3
rollup: statistics_customer_superior
tabletRatio=6/6
tabletList=216436,216440,216444,216448,216452,216456
cardinality=89
avgRowSize=30.079765
numNodes=0

- c1的结果集合b的结果集添加broadcast或者shuffle关键字，关联出来的结果返回行数是正常的不会变化，而未加关键字返回的结果是会变化的，麻烦看看是不是这个版本join的一个bug