【 使用环境 】生产环境
【 OB or 其他组件 】oceanbase，4.2.4版本，4.3.3版本，同一个SQL在4.2.3版本可以正常执行，4.2.4版本，4.3.3版本出现“Error Code: 4013. No memory or reach tenant memory”
【 使用版本 】4.3.3
【问题描述】请见相关SQL。
【复现路径】
SQL 如下，其实问题很简单，就是 in 条件的ID 非常多。在4.2.3版本执行是没问题的，并且执行速度是2s左右。在4.2.4版本就出现“Error Code: 4013. No memory or reach tenant memory”的问题。感觉是有bug吗？
use test;
SELECT
kh.id_ AS id,
kh.is_dzh_ AS isDzh,
kh.zzsnslx_ AS zzsnslx,
kh.level_ AS level,
kh.fw_status_ AS fwStatus,
@currentMonth:=DATE_FORMAT(NOW(), '%Y%m') currKjQj,
@fwgdLrDate:=(SELECT
MIN(tr.create_date_)
FROM
xx fwgd
INNER JOIN
xx tr ON fwgd.id_ = tr.new_ht_fwgd_id_
WHERE
fwgd.kh_khxx_id_ = kh.id_
AND tr.new_zj_zjxx_id_ = 'h0000000000000498723430515097600') AS fwgdLrDate,
@fwqxZ:=MAX(IF(ht.status_ != '4', '', ht.fwqx_z_)) AS fwqxZ,
@fwqxQ:=IFNULL((SELECT
htxx.fwqx_q_
FROM
xx htxx
WHERE
htxx.id_ = hf.ht_htxx_id_),
MIN(ht.fwqx_q_)) AS fwqxQ,
@shDate:=MIN(ht.sh_date_) AS shDate,
@tkwcRq:=(SELECT
MAX(tk.tkwc_rq_)
FROM
xx ht1
LEFT JOIN
xx tk ON ht1.id_ = tk.ht_id_ AND tk.tkzt_ = 3
WHERE
ht1.kh_khxx_id_ = kh.id_) tkwcRq,
@wqWcrq:=(SELECT
IF(MAX(mx.sycs_) <= 0
AND GROUP_CONCAT(DISTINCT taskfwsx.status_) = 8,
MAX(taskfwsx.confirm_time_),
IF(MAX(mx.sycs_) <= 0
AND GROUP_CONCAT(DISTINCT taskfwsx.status_) IS NULL,
mx.finish_date_,
NULL))
FROM
xx mx
INNER JOIN
xx wqfwsx ON wqfwsx.id_ = mx.wq_fwsx_id_
AND wqfwsx.wqlx_ != 0
LEFT JOIN
xx taskfwsx ON taskfwsx.ht_fwsxmx_id_ = mx.id_
LEFT JOIN
xx tf ON tf.ht_fwsxmx_id_ = mx.id_
AND tf.tkzt_ = 3
WHERE
mx.ht_htxx_id_ = ht.id_
AND mx.zf_zt_ = 0
AND tf.id_ IS NULL) wqWcRq,
@initialStatus:=IF((init.initial_status_ = '1')
AND EXISTS( SELECT
1
FROM
xx khfw
WHERE
kh.id_ = khfw.kh_khxx_id_ AND yjjz_ = 1)
AND EXISTS( SELECT
1
FROM
xx ht
WHERE
ht.kh_khxx_id_ = kh.id_
AND ht.status_ = 4
AND ht.htlx_ IN ('20' , '30', '40')
AND kh.is_dzh_ != 0),
1,
IF(EXISTS( SELECT
1
FROM
xx khfw
WHERE
kh.id_ = khfw.kh_khxx_id_ AND yjjz_ = 1)
AND EXISTS( SELECT
1
FROM
xx ht
WHERE
ht.kh_khxx_id_ = kh.id_
AND ht.status_ = 4
AND ht.htlx_ IN ('20' , '30', '40')
AND kh.is_dzh_ != 0),
0,
NULL)) AS initialStatus,
IF(ht.id_ IS NOT NULL,
(SELECT
pj.fxkh_
FROM
xx pj
WHERE
pj.kh_khxx_id_ = kh.id_),
NULL) AS fxStatus,
MAX(IF(EXISTS( SELECT
1
FROM
xx ht1
LEFT JOIN
xx tk ON tk.ht_id_ = ht1.id_
LEFT JOIN
xx tkyy ON tk.id_ = tkyy.ht_tkxx_id_
LEFT JOIN
xx xf ON ht1.id_ = xf.ht_htxx_id_
LEFT JOIN
xx ty ON ty.kh_khxx_id_ = ht1.kh_khxx_id_
WHERE
ht1.id_ = ht.id_
AND (tk.lczt_ = '5'
AND tkyy.tkyy_code_value_ IN ('0409' , '0410', '0411', '0412')
OR (xf.xfyx_ = '0' AND xf.bxfzt_ = '3'
AND ty.ty_yy_label_ IN ('0409' , '0410', '0411', '0412')))),
1,
0)) AS gwStatus,
@zhStatus:=IF(hf.wxjz_ = '1' OR hf.yjjz_ = '1',
1,
0) AS zhStatus,
@yjjz:=IF(hf.wxjz_ = '1',
2,
IF(hf.yjjz_ = '1', 1, 0)) AS yjjz,
zh.dfp_zh_ AS dfpZh,
zh.dfp_jz_ AS dfpJz,
zh.dfp_status_ AS dfpStatus,
IF(zt.id_ IS NOT NULL, 1, 0) AS defaultZt,
IF(@yjjz != 0,
IF(DATE_FORMAT(hf.yjjz_date_, '%Y%m') = @currentMonth,
@yjjz,
NULL),
NULL) AS dyYzhFs,
IF(@zhStatus = 0,
IF(EXISTS( SELECT
1
FROM
xx er
WHERE
er.kh_khxx_id_ = kh.id_),
1,
0),
NULL) AS whWzhyyStatus,
TIMESTAMPDIFF(DAY,
@shDate,
IFNULL(hf.yjjz_date_, NOW())) AS zhZq,
@scgt:=IF(EXISTS( SELECT
1
FROM
xx hf1
WHERE
hf1.kh_khxx_id_ = kh.id_
AND hf1.scgt_ = 1),
1,
0),
IF(@zhStatus = 0,
IF(zh.hs_qylxr_type_ IN ('1' , '2', '3')
AND (EXISTS( SELECT
1
FROM
xx hsxx
WHERE
hsxx.kh_khxx_id_ = kh.id_)
OR NOT EXISTS( SELECT
1
FROM
xx ht
INNER JOIN
xx mx ON ht.id_ = mx.ht_htxx_id_
LEFT JOIN
xx htf ON mx.id_ = htf.ht_fwsxmx_id_
AND htf.tkzt_ = 3
LEFT JOIN
xx hs ON hs.kh_khxx_id_ = ht.kh_khxx_id_
AND ht.id_ = hs.ht_htxx_id_
WHERE
mx.kh_khxx_id_ = kh.id_
AND ht.status_ IN (4)
AND htf.id_ IS NULL
AND hs.hs_type_ IS NULL)),
2,
IF(EXISTS( SELECT
1
FROM
xx er
JOIN
xx value ON er.code_ = value.value1_
AND value.infra_code_type_id_ = 'h0000000000000033091146981793793'
WHERE
er.kh_khxx_id_ = kh.id_
AND value.status_ = '1'
AND value.value1_ IN ('E031' , 'E100', 'E014', 'E036', 'E103')
AND er.deal_progress_ NOT IN ('3' , '6', '5')
AND er.is_jfyc_ = '1'),
0,
1)),
0) AS dzhScgtStatus,
zh.hs_qylxr_date_ dzhScgtDate,
(SELECT
MIN(hsxx.create_date_)
FROM
xx hsxx
WHERE
hsxx.kh_khxx_id_ = kh.id_) AS hsHtxxDate,
IF(@yjjz = 1,
IF(init.cwscgt_ = 1, 2, 1),
0) AS dgwScgtStatus,
init.cwscgt_date_ AS csScgtDate,
hf.yjjz_date_ AS zhDate,
IF(EXISTS( SELECT
1
FROM
xx
WHERE
infra_khbq_id_ = 'h0000000000000417138514390507521'
AND kh_khxx_id_ = kh.id_),
1,
0) AS tyd,
CASE
WHEN hf.wxjz_ = '1' THEN 4
WHEN
@yjjz = '1'
THEN
CASE
WHEN
zt.id_ IS NOT NULL
AND @initialStatus = '1'
THEN
7
WHEN
zt.id_ IS NOT NULL
AND IFNULL(@initialStatus, '0') != '1'
THEN
6
WHEN zt.id_ IS NULL THEN 5
END
WHEN
kh.is_dzh_ = '1'
AND IFNULL(hf.yjjz_, '0') = '0'
THEN
1
WHEN
IFNULL(kh.is_dzh_, '0') = '0'
AND IFNULL(hf.wxjz_, '0') = '0'
THEN
2
END AS zhjd,
IF(EXISTS( SELECT
1
FROM
xx ht1
WHERE
ht1.kh_khxx_id_ = kh.id_
AND ht1.status_ = 4
AND ((ht1.qd_sc_ IS NOT NULL
AND ht1.qd_sc_ > 0)
OR (ht1.cxsc_ IS NOT NULL AND ht1.cxsc_ > 0)
OR (ht1.zssc_ IS NOT NULL AND ht1.zssc_ > 0)
OR (ht1.lzsc_ IS NOT NULL AND ht1.lzsc_ > 0))),
1,
0) AS dzlzSc,
IF(EXISTS( SELECT
1
FROM
xx mx
INNER JOIN
xx wf ON wf.id_ = mx.wq_fwsx_id_
AND wf.kind_ IN ('04' , '02', '05')
LEFT JOIN
xx tk ON mx.id_ = tk.ht_fwsxmx_id_
AND tk.tkzt_ = 3
WHERE
tk.id_ IS NULL
AND mx.kh_khxx_id_ = kh.id_
AND (mx.finish_status_ != 1
AND mx.zf_zt_ != 1
AND (mx.ht_htxx_id_ = '00000000000000000000000000000000'
OR (EXISTS( SELECT
1
FROM
ftsp_ht_htxx h
WHERE
h.id_ = mx.ht_htxx_id_
AND h.status_ = '4'))))),
1,
0) AS fwsxType,
kh.ht_fw_zt_ htFwZt,
IF(EXISTS( SELECT
1
FROM
xx ht1
WHERE
ht1.kh_khxx_id_ = kh.id_
AND ht1.status_ IN (4 , 8)),
1,
0) AS khzhStatus,
EXISTS( SELECT
1
FROM
xx pg
INNER JOIN
xx u ON u.id_ = pg.infra_user_id_
WHERE
u.is_delete_ = '0'
AND u.role_code_ LIKE CONCAT('%', 'PORTAL_ZBKJ_GWS', '%')
AND pg.kh_khxx_id_ = kh.id_) AS zzkj,
EXISTS( SELECT
1
FROM
xx pg
INNER JOIN
xx u ON u.id_ = pg.infra_user_id_
WHERE
u.is_delete_ = '0'
AND u.role_code_ LIKE CONCAT('%', 'PORTAL_CSGW_GWS', '%')
AND pg.kh_khxx_id_ = kh.id_) AS csgw,
(SELECT
jcxx.sfzxh_
FROM
ftsp_kh_jcxx jcxx
WHERE
jcxx.kh_khxx_id_ = kh.id_) AS zxh,
hf.fw_lx_ fwLx,
DATE_FORMAT(ty_date_, '%Y%m') tyDate,
zh.jfyc_status_ jfycStatus,
IF(EXISTS( SELECT
1
FROM
xx er
JOIN
xx value ON er.code_ = value.value1_
AND value.infra_code_type_id_ = 'h0000000000000033091146981793793'
WHERE
er.kh_khxx_id_ = kh.id_
AND value.status_ = '1'
AND value.value1_ IN ('E037' , 'E031', 'E100', 'E014', 'E036')
AND er.deal_progress_ NOT IN ('3' , '6', '5')
AND er.is_jfyc_ = '1'),
1,
0) tcJfycStatus,
IF(EXISTS( SELECT
1
FROM
xx er
WHERE
er.kh_khxx_id_ = kh.id_
AND er.deal_progress_ = '5'),
1,
0) jfycDhs
FROM
a kh
LEFT JOIN
b ht ON ht.kh_khxx_id_ = kh.id_
AND ht.status_ = 4
LEFT JOIN
c zh ON zh.kh_khxx_id_ = kh.id_
LEFT JOIN
d hf ON hf.kh_khxx_id_ = kh.id_
AND hf.fw_lx_ = '1'
LEFT JOIN
e zt ON zt.kh_khxx_id_ = kh.id_
AND zt.is_default_ = '1'
LEFT JOIN
f init ON init.kh_khxx_id_ = kh.id_
WHERE
kh.zj_zjxx_id_ = 'h0000000000000498723430515097600'
AND kh.fw_status_ = '1'
AND kh.id_ IN (
'34236668E4F24A2194078B06A41FB3E9',
'h0000000000000723719839150735360',
'h0000000000000723721895296237568',
'h0000000000000723725015971635200',
'h0000000000000723725944125022208',
'h0000000000000723727762867486720',
'h0000000000000723027926701416448',
'FAE0ADFBC45C4CC49A43812C0D85886D',
'h0000000000000723740577568088064',
'h0000000000000723764224533954560',
'h0000000000000414686761562685610',
'h0000000000000723770814864670720',
'h0000000000000721635630453366784',
'A801EC5C41CC4498AFB9CC7C3B99C6FE',
'h0000000000000270905798948709064',
'h0000000000000723773802352967680',
'h0000000000000723777364676009984',
'h0000000000000723724900702601216',
'h0000000000000723792559037177856',
'h0000000000000723793193538904064',
'h0000000000000718746453054595072',
'h0000000000000723795291613667328',
'h0000000000000723809000746622976',
'A6A60FD955234A96A18EEE6BECC28340',
'h0000000000000723813892685021184',
'h0000000000000723819395545972736',
'h0000000000000723828924156182528',
'28F140F099A54CE7820E26C72D0766FB',
'D4E8A86271B2444CAEEA782D1BD57662',
'457A44E783FE424FBDB3A05881842E4A',
'h0000000000000302974729383772160',
'h0000000000000304426901648629760',
'h0000000000000723864891199176704',
'h0000000000000304444614374809600',
'h0000000000000304445703337730048',
'3C426DB4C422412487B4B0F257D511A2',
'9AB2904F320B44F78D380A0EDE8FB777',
'9424C1F306024F09A43B3D55C5F01998',
'h0000000000000304478707036643328',
'h0000000000000254492324970897536',
'13fb3f5c610545d58da169ef29c38b01',
'h0000000000000292995586075181067',
'6F18F2BE57284C6A99D9FA9AFED0761B',
'h0000000000000254249655644234017',
'h0000000000000304516426437713920',
'h0000000000000297169967694233600',
'h0000000000000279888233080111591',
'h0000000000000297681196213616719',
'h0000000000000257686961026138473',
'h0000000000000267985696891675845',
'8B3E36C081254D8CA9591E2558CCE090',
'h0000000000000304565098089226240',
'h0000000000000304568441587048448',
'h0000000000000125535160930034047',
'0534221B7D4541CCACE6090964500285',
'h0000000000000724061446504931328',
'h0000000000000034450037090623488',
'B22F868B0B764A09A1428AFF5A2CBCBC',
'ACAD193F8F5A411595D98F8503E77F99',
'0CE656BD22AF4C2EB9176EF4BDC458DD',
'h0000000000000724154007051550720',
'h0000000000000724162090797785088',
'h0000000000000542736819524067800',
'h0000000000000107032479624822807',
'h0000000000000483171988757233664',
'1C5E142BF6B04498AF1E423B78298F59',
'h0000000000000724210513726070784',
'h0000000000000304773993869008896',
'h0000000000000302634834664579072',
'h0000000000000302614239776268288',
'h0000000000000142822591766438017',
'6ECAE3DE7B194C5990D382AFD072F2C8',
'h0000000000000170729408827809792',
'14869805049584908244 ',
'E6C0D181AA8947A0AB9982D99E05D8D2',
'h0000000000000304843433277054976',
'h0000000000000303417233084768256',
'h0000000000000269991955040755816',
'h0000000000000181081137609793652',
'14767748520687034455 ',
'h0000000000000304864450133286912',
'h0000000000000300424046572700153',
'h0000000000000304552293726380032',
'251558c660904d35b1ceb4d4a5d15dea',
'833E37CADC87456B9A97F8C7D5502091',
'h0000000000000304899210738106368',
'h0000000000000234191610981548582',
'h0000000000000274397987003998301',
'h0000000000000230347561999212544',
'h0000000000000054110362505454764',
'DDCD480FD80845BFA4A6132FB853B161',
'h0000000000000304796305745969152',
'h0000000000000304931530857717760',
'h0000000000000304911545158541312',
'29A0A978077C4A5F8DDD69E3B294C90B',
'h0000000000000294597953715781643',
'h0000000000000724440982204850176',
'2B3FFFD5BFA34E05811FB5F029FF9440',
'h0000000000000082753401467142149',
'4B15260A35644CB68530BF4582A6CD81',
'h0000000000000724499642741374976',
'h0000000000000724499149424074752',
'h0000000000000724507527596654592',
'h0000000000000724507481748725760',
'h0000000000000724518956118761472',
'h0000000000000724520333633077248',
'h0000000000000724522237779943424',
'E820CD5DBC6D4402A654C755B7BE0DCA',
'h0000000000000723507205084266496',
'h0000000000000724533114579107840',
'h0000000000000724518650917027840',
'h0000000000000724536037884149760',
'h0000000000000724542432900759552',
'h0000000000000722985982956773377',
'36AD690B50D64E0B98C38CD8ACEEFCDD',
'h0000000000000305141955264200704',
'h0000000000000275807199836087798',
'h0000000000000305157188418486272',
'h0000000000000243369829867806815',
'8F92145936F24BF4B228A73107205F50',
'h0000000000000259950636164071424',
'h0000000000000305156806963306496',
'h0000000000000305164730276208640',
'h0000000000000724452729526632448',
'922f740ff7a24dd58bb97f5718f50fda',
'5CA449B71CBB4B1D8B41339A1831B78B',
'h0000000000000265768311404708555',
'h0000000000000305205333475155968',
'ABA9023D43F44E15B3F34B15690ABEC9',
'h0000000000000304844025307267072',
'h0000000000000247329166550860289',
'AD76E7089DD7467197C778332D4A9763',
'h0000000000000053711454633566664',
'h0000000000000241550432005587078',
'B89C82C1C91B4F72B4DB99A22D29C22A',
'2ab24d0af4014775984f30793d9716b2',
'h0000000000000305163873877737472',
'C869F44C9BA045ACB1C9A627F8E7453E',
'h0000000000000305243916105867264',
'h0000000000000257420134761022212',
'h0000000000000305243144223940608',
'h0000000000000305258433409916928',
'h0000000000000305211971179347968',
'CEEBA0364DCE486FAE36F2796F9223F7',
'h0000000000000305274520881037312',
'9022E50CCFA64F6C859FA7FEEB3CA0DB',
'9AE506969B2D4C33A0108B245870FDF4',
'03107BA2FE554DACA9EA2F4066ACE557',
'h0000000000000724840477185974272',
'4734548F09444FA89EF5A316FEF06316',
'E390DFF439CC4FE0BD699B43FB0CECA7',
'h0000000000000247329166538276896',
'69995747147646c3a18ef5172c3f9d26',
'h0000000000000259217775795143043',
'4F0E20713D234FF5A27A143B4960C518',
'h0000000000000724946731174641664',
'h0000000000000304877872127164416',
'h0000000000000305535603109724160',
'h0000000000000137103803694621229',
'h0000000000000305536638385586176',
'h0000000000000305547060790624256',
'F6E9B1495F1C4B3A8D56F8FF93C46DD9',
'h0000000000000289564115418497028',
'h0000000000000200775292788171057',
'h0000000000000258186906250707028',
'h0000000000000305212086354911232',
'h0000000000000305574162118172672',
'h0000000000000305562146032787456',
'h0000000000000302037093272027350',
'h0000000000000299805761412096000',
'h0000000000000305587154700435456',
'h0000000000000304582043475853312',
'h0000000000000305564225468047360',
'h0000000000000081332275838935040',
'h0000000000000305612560690274304',
'h0000000000000247326428848955546',
'h0000000000000257653388575440966',
'h0000000000000297535879455450011',
'h0000000000000283193772351971771',
'h0000000000000305635742063214592',
'FA588DC9A7024174B11D6FABC5DBF555',
'h0000000000000125871690378739945',
'h0000000000000275772998730514850',
'h0000000000000175190294598508906',
'C7005C33EF584747A2473AD794B7D33E',
'BCCFB1B24C9149E3BC9ECCB07D5795E7',
'C38E8719202544B5B88BC8C4E838D3C9',
'77A7BA60CFAE446D861ACB13255055F2',
'F3C0F26450644D6BAFEE2726F9732644',
'h0000000000000300197292015042560',
'h0000000000000305876303152119808',
'h0000000000000305875145008300032',
'h0000000000000305875232283377664',
'h0000000000000275773011120431136',
'h0000000000000260562279752376536',
'h0000000000000305892253305888768',
'h0000000000000263668641769185803',
'DEE56745A6D84C518761DAE721351C68',
'h0000000000000305892335417745408',
'h0000000000000299333779395903771',
'h0000000000000268920993645601632',
'h0000000000000305929428810530816',
'h0000000000000305892222356094976',
'h0000000000000302970516343832576')
GROUP BY kh.id_;
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！
