Pain Sun created IMPALA-11061:
---------------------------------
Summary: Multi-aggregation functions cause a crash
Key: IMPALA-11061
URL: https://issues.apache.org/jira/browse/IMPALA-11061
Project: IMPALA
Issue Type: Bug
Components: Backend
Affects Versions: Impala 3.4.0
Environment: centos
Reporter: Pain Sun
A query with multi-aggregation functions cause a crash.I update the version to
3.4.0 and update the code as IMPALA-9809 and compiled.But I still cause the bug.
Log:
# A fatal error has been detected by the Java Runtime Environment:
#
# SIGSEGV (0xb) at pc=0x000000000152e2d8, pid=6301, tid=0x00007f5c425a1700
#
# JRE version: Java(TM) SE Runtime Environment (8.0_202-b08) (build
1.8.0_202-b08)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.202-b08 mixed mode linux-amd64
compressed oops)
# Problematic frame:
# C [impalad+0x112e2d8]
impala::AggFnEvaluator::SerializeOrFinalize(impala::Tuple*,
impala::SlotDescriptor const&, impala::Tuple*, void*)+0x48
#
# Core dump written. Default location: /var/lib/impala/core or core.6301
#
# An error report file with more information is saved as:
# /var/lib/impala/hs_err_pid6301.log
#
# If you would like to submit a bug report, please visit:
# http://bugreport.java.com/bugreport/crash.jsp
Sql:
with resultSrc as (
select
if(
resultnotMob.mobile is not null,
resultnotMob.mobile,
platMob.mobile
) as mobile,
resultnotMob.sellernick,
resultnotMob.buyernick,
resultnotMob.shopType,
resultnotMob.clientid
from
(
select
selleridtab1.sellernick,
crow.buyernick,
crow.shopType,
crow.clientid,
crow.mobile
from
(
select
sellernick,
buyernick
from
member.sibn
where
mainsellernick = 'a'
and buyernick != ''
and buyernick is not null
and sellernick != ''
and sellernick is not null
and sellernick in('a')
) as selleridtab1
right join [shuffle] (
SELECT
mergeClientIdt1.clientid,
mergeClientIdt1.mobile,
if(
memberRelationt2.buyernick is not null,
memberRelationt2.buyernick,
mergeClientIdt1.buyernick
) as buyerNick,
if(
memberRelationt2.shoptype is not null,
memberRelationt2.shoptype,
mergeClientIdt1.shoptype
) as shopType
from
(
SELECT
result.buyernick,
result.shoptype,
result.mobile,
IF(
memberRelationt1.clientid is not NULL,
memberRelationt1.clientid,
concat(
cast(result.shoptype AS string),
result.buyernick
)
) AS clientid
FROM
(
select
buyerNick,
cast(shopType AS string) AS shopType,
mobile
FROM
(
WITH Tradewithgroup as (
select
buyerNick,
cast(shoptype AS string) AS
shopType,
if(shopType in ('2', '3',
'4', '34'), null, mobile) as mobile
from
(
select
cast(shopType AS
string) AS shopType,
buyerNick,
max(mobile) as
mobile
from
(
select
case
when
sellerNick in('') then '2'
end as
shoptype,
buyerNick,
sellerNick,
tid,
max(m1) as
m1,
max(receivermobile) as mobile,
max(day) as
day,
max(trade_payment) as trade_payment,
sum(if(refund_fee is null, 0, refund_fee)) as refund_fee,
sum(num) as
num,
max(
trade_payment - if(refund_fee is null, 0, refund_fee)
) as
maxtradepaymentrefund,
count(if(refund_fee is not null, oid, null)) as refund_count,
min(created) as min_created,
max(created) as max_created,
min(paytime) as min_paytime,
max(paytime) as max_paytime
from
trade.tci
where
mainsellernick = ''
and
buyerNick != ''
and
sellerNick in('')
group by
sellerNick,
buyerNick,
tid
) as allresult
group by
buyerNick,
shoptype
having
((sum(trade_payment) = 687.15))
and (
((sum(trade_payment) - sum(refund_fee)) = 687.15)
)
and
((count(distinct(day)) = 3))
and
((count(distinct(tid)) = 3))
and ((sum(num) = 9))
and
((sum(refund_fee) = 0))
and
((sum(refund_count) = 0))
and (
(
min(min_created) >= '2021-11-02 00:00:00'
and
min(min_created) <= '2021-11-02 23:00:00'
)
)
and (
(
(sum(trade_payment) / count(distinct(day))) >= 229.05
)
)
) as tradeGroup
),
RFMwithgroup as (
select
cast(shoptype AS string) AS
shopType,
buyerNick
from
(
select
platmember00.buyerNick,
platmember00.shopType
from
(
select
cast(shopType AS string) AS shopType,
buyerNick
from
(
select
cast(shopType AS string) AS shopType,
nick AS buyerNick
FROM
member.cmdn
WHERE
isdelete = 0
AND
mainsellernick = ''
and
channelid in (
SELECT
channelid
from
member.cmdi
where
(
MainSellerNick = ''
and isdelete = 0
and (
(LastPayment >= 172.51)
and (YearTradeCount >= 2)
and (YearTradeAmount = 413.55)
and (TotalPayment >= 413.55)
and (TotalPayCount = 2)
and (FirstPayment = 241.04)
and (
FirstPaytime >= '2021-11-02 00:00:00'
and FirstPaytime <= '2021-11-02 23:59:59'
)
and (
LastPaytime >= '2021-12-12 00:00:00'
and LastPaytime <= '2021-12-12 23:59:59'
)
)
)
)
) as channel
) as platmember00
) as RFMGroup
),
Basewithgroup as (
select
cast(shoptype AS string) AS
shopType,
buyerNick
from
(
SELECT
platmemberHXAll.shopType,
platmemberHXAll.buyerNick
FROM
(
SELECT
cast(shopType AS string) AS shopType,
buyerNick
FROM
(
SELECT
IF (
platmemberHXAndClientID.clientid IS NOT NULL,
integralThirdNickRelationTable2.shopType,
platmemberHXAndClientID.shopType
)
AS shopType,
IF (
platmemberHXAndClientID.clientid IS NOT NULL,
integralThirdNickRelationTable2.buyerNick,
platmemberHXAndClientID.buyerNick
)
AS buyerNick,
platmemberHXAndClientID.clientid
FROM
(
SELECT
platmemberHX.shopType,
platmemberHX.buyerNick,
integralThirdNickRelationTable.clientid
FROM
(
select
cast(shopType AS string) AS shopType,
thirdnick as buyerNick
from
member.cmdhi
where
mainshopnick = ''
and isdelete = 0
and thirdnick != ''
and thirdnick is not null
and shopType != 56
) as platmemberHX
LEFT JOIN [shuffle] (
SELECT
clientid,
cast(shopType AS string) AS shopType,
thirdnick AS buyerNick
FROM
member.itnri
WHERE
brandid IN(1)
and thirdnick != ''
and thirdnick is not null
AND isdelete = 0
) as integralThirdNickRelationTable ON
integralThirdNickRelationTable.shopType = platmemberHX.shopType
AND integralThirdNickRelationTable.buyerNick = platmemberHX.buyerNick
)
AS platmemberHXAndClientID
LEFT JOIN [shuffle] (
SELECT
clientid,
cast(shopType AS string) AS shopType,
thirdnick AS buyerNick
FROM
member.itnri
WHERE
brandid IN(1)
and thirdnick != ''
and thirdnick is not null
AND isdelete = 0
)
as integralThirdNickRelationTable2 ON platmemberHXAndClientID.clientid =
integralThirdNickRelationTable2.clientid
) AS allr
UNION
all
SELECT
hx.shoptype,
qy.thirdnick as buyerNick
FROM
(
SELECT
cast(shopType AS string) AS shopType,
split_part (buyerNick, ':', 1) AS corpid,
split_part (buyerNick, ':', 2) AS externaluserid
FROM
(
select
cast(shopType AS string) AS shopType,
thirdnick as buyerNick
from
member.cmdhi
where
mainshopnick = 'a'
and isdelete = 0
and thirdnick != ''
and thirdnick is not null
and shopType = 56
)
as hx1
) AS hx
LEFT JOIN [
shuffle ] (
SELECT
clientid AS externaluserid,
ownercorpid AS corpid,
thirdnick
FROM
member.qeudi
WHERE
mainshopnick = 'a'
) AS qy ON
hx.corpid = qy.corpid
AND
hx.externaluserid = qy.externaluserid
) AS platmemberHXAll
inner join
[shuffle] (
select
channelMemberNickGroup.buyerNick,
channelMemberNickGroup.shopType,
channelMemberNickGroup.channelid
from
(
select
nick as buyerNick,
cast(shopType AS string) AS shopType,
channelid
from
member.cmdn
where
mainsellernick = 'a'
and
isdelete = 0
and
nick != ''
and
nick is not null
) as
channelMemberNickGroup left semi
join [
shuffle ] (
select
othercon.channelid
from
(
select
channelid
from
member.cmdi
where
mainSellerNick = 'a'
and isdelete = 0
and channelid != ''
and channelid is not null
and (
(firstPayShop in('123'))
and (lastPayShop in('123'))
)
)
as othercon
inner join [ shuffle ] (
select
channelid
from
member.cmdps
where
mainSellerNick = 'a'
and isdelete = 0
and channelid != ''
and channelid is not null
and payshop in('123')
)
as payshopcon on othercon.channelid = payshopcon.channelid
) as
sinqlgroup on channelMemberNickGroup.channelid = sinqlgroup.channelid
) as
channelmemberMember2021 on channelmemberMember2021.shopType =
platmemberHXAll.shopType
and
channelmemberMember2021.buyerNick = platmemberHXAll.buyerNick
) as BaseGroup
)
select
Tradewithgroup.buyerNick,
Tradewithgroup.shopType,
max(mobile) as mobile
from
Tradewithgroup
inner join [shuffle]
RFMwithgroup on Tradewithgroup.buyerNick = RFMwithgroup.buyerNick
and Tradewithgroup.shopType =
RFMwithgroup.shopType
inner join [shuffle]
Basewithgroup on RFMwithgroup.buyerNick = Basewithgroup.buyerNick
and RFMwithgroup.shopType =
Basewithgroup.shopType
group by
Tradewithgroup.buyerNick,
Tradewithgroup.shopType
) AS mergeMemberResultSQL
) AS result
LEFT JOIN [ shuffle ] (
SELECT
clientid,
cast(shoptype AS string) AS shopType,
thirdnick AS buyerNick
FROM
member.itnri
WHERE
brandid IN(1)
AND isdelete = 0
and thirdnick != ''
and thirdnick is not null
) AS memberRelationt1 ON result.shoptype =
memberRelationt1.shoptype
AND result.buyernick =
memberRelationt1.buyernick
) as mergeClientIdt1
LEFT JOIN [ shuffle ] (
SELECT
clientid,
cast(shoptype AS string) AS shopType,
thirdnick AS buyerNick
FROM
member.itnri
WHERE
brandid IN(1)
AND isdelete = 0
and thirdnick != ''
and thirdnick is not null
) AS memberRelationt2 ON mergeClientIdt1.clientid =
memberRelationt2.clientid
) as crow on selleridtab1.buyernick = crow.buyernick
) as resultnotMob
left join [ shuffle ] (
select
mobile,
buyernick,
cast(shopType AS string) AS shopType
from
member.pmdi
where
shoptype not in (2, 3, 4, 34)
and mainsellernick = 'a'
and buyerNick != ''
and buyerNick is not null
) as platMob on resultnotMob.buyernick = platMob.buyernick
and resultnotMob.shopType = platMob.shopType
),
resultAllOther as (
SELECT
clientid,
GROUP_CONCAT(
DISTINCT (
CASE
WHEN shoptype = '2' THEN (
concat(
if(
sellernick is null,
'err',
cast(sellernick AS string)
),
'_adv_qry_sep_1_',
buyerNick
)
)
END
),
'_adv_qry_sep_2_'
) AS taoBaoouid,
max(
CASE
WHEN shoptype = '4' THEN buyerNick
END
) AS jdnick,
max(
CASE
WHEN shoptype = '13' THEN buyerNick
END
) AS yzId,
GROUP_CONCAT(
DISTINCT (
CASE
WHEN shoptype = '34' THEN (
concat(
if(
sellernick is null,
'err',
cast(sellernick AS string)
),
'_adv_qry_sep_1_',
buyerNick
)
)
END
),
'_adv_qry_sep_2_'
) AS dYId,
max(
CASE
WHEN shoptype = '20' THEN buyerNick
END
) AS wMId,
max(
CASE
WHEN shoptype = '1' THEN buyerNick
END
) AS mobile1,
max(mobile) AS mobile2,
max(
CASE
WHEN shoptype = '56' THEN buyerNick
END
) AS externalUserId,
max(
CASE
WHEN shoptype = '35' THEN buyerNick
END
) AS unionid,
max(
CASE
WHEN shoptype <> '2'
AND shoptype <> '4'
AND shoptype <> '13'
AND shoptype <> '34'
AND shoptype <> '20'
AND shoptype <> '1'
AND shoptype <> '56'
AND shoptype <> '35' THEN buyerNick
END
) AS othernick
FROM
resultSrc
GROUP BY
clientid
)
select
resultAllOther.taoBaoouid as taoBaoouid,
resultAllOther.jdnick as jdnick,
resultAllOther.yzId as yzId,
resultAllOther.dYId as dYId,
resultAllOther.wMId as wMId,
IF (
resultAllOther.mobile1 is not NULL,
resultAllOther.mobile1,
resultAllOther.mobile2
) AS mobile,
resultAllOther.othernick as othernick,
resultAllOther.externalUserId as externalUserId,
resultAllOther.unionid as unionid
from
resultAllOther;
I don't know where cause this,so I put all here.
it seems like IMPALA-9809 but I have already fix it ,still cause crash.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
