关联更新 SQL报错：4016-internal error

[AntTech_DZYC6F]

【 使用环境 】测试环境
【 OB or 其他组件 】obmysql
【 使用版本 】5.7.25-OceanBase-v4.2.1.6
【问题描述】执行如下sql报错：

加一个不影响逻辑的条件 a.inter_code > 0 就好了，改动后sql：

【复现路径】问题出现前后相关操作
【附件及日志】

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！
@论坛小助手

[论坛小助手]

用户问题: “在 OceanBase 版本 5.7.25-OceanBase-v4.2.1.6 测试环境中执行更新 SQL 时遇到错误 4016-internal error，添加不影响逻辑的条件 a.inter_code > 0 后问题解决，需要进一步诊断该错误原因。”

推荐使用 obdiag 工具进行根因分析和日志采集，以定位问题的具体原因，并将采集到的日志提供给我们以便确认问题所在。

日志采集场景: SQL 执行出错
日志采集命令: obdiag gather scene run --scene=observer.sql_err --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

另外，为了确定具体的问题，在此需要您提供一些信息：

1. 能否提供遇到错误 4016 的 SQL 语句及其上下文？
2. 能否提供该错误发生的时间点？
3. 是否有其他的错误信息或警告信息伴随出现？
4. 系统在该时间段内的负载情况如何？是否有其他操作同时进行？
5. 能否提供该测试环境的配置信息，如 CPU、内存、磁盘等？

请使用上述命令采集日志，并将生成的日志压缩包发送给我们，我们将进一步分析问题的原因。感谢您的配合。

附上敏捷诊断工具 obdiag 使用帮助链接

[独善其身]

语法上看不出什么错误来啊,数据量太大了吗？

[obtp]

学习了

[Sunshining]

每天都在学习中

[皇甫侯]

最好贴下执行计划，看看是不是走了 NLJ BATCH 引起的问题

https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000438674

[AntTech_DZYC6F]

才2k+

[AntTech_DZYC6F]

执行计划如下：

=====================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |UPDATE | |7 |695 |
|1 |└─HASH RIGHT ANTI JOIN | |7 |619 |
|2 | ├─SUBPLAN SCAN |VIEW1 |1 |6 |
|3 | │ └─TABLE RANGE SCAN |b(qy_idx_tmktdt00_md002) |1 |6 |
|4 | └─MERGE ANTI JOIN | |8 |610 |
|5 | ├─NESTED-LOOP JOIN | |8 |527 |
|6 | │ ├─TABLE FULL SCAN |a |23 |524 |
|7 | │ └─MATERIAL | |1 |3 |
|8 | │ └─TABLE GET |c |1 |3 |
|9 | └─SORT | |1 |82 |
|10| └─SUBPLAN SCAN |VIEW2 |1 |82 |
|11| └─TABLE RANGE SCAN|c(temp_qy_uniq_totherstockinfo)|1 |82 |

Outputs & filters:

0 - output(nil), filter(nil)
table_columns([{a: ({qy_tstockinfo: (a.inter_code, a.business_date, a.market_no, a.stock_type, a.asset_type, a.report_code, a.stock_name, a.stock_fullname,
a.stock_spell, a.stock_status, a.underwriter_id_list, a.wx_listing_date, a.listing_date, a.frozen_code, a.asset_relative_code, a.match_relative_code, a.issuer_id,
a.trade_currency_no, a.settle_currency_no, a.uplimited_amount, a.downlimited_amount, a.buy_unit, a.sale_unit, a.amount_per_hand, a.uplimited_ratio, a.downlimited_ratio,
a.transfer_mode, a.stb_trans_status, a.total_share, a.outstanding_share, a.non_turnover_share, a.reference_price, a.mmbz, a.board_type, a.long_stop_flag,
a.marketprice_order_max, a.marketprice_order_min, a.issue_total_share, a.publish_date, a.gzstock_layer, a.mktp_buy_amount_unit, a.mktp_sale_amount_unit,
a.no_profit_flag, a.voting_right_diff_flag, a.regist_system_flag, a.vie_flag, a.stock_stop_flag, a.isin_code, a.pe, a.non_public_issue_flag, a.turnover_date,
a.online_estimated_ratio, a.offline_estimate_ratio, a.policy_org_flag, a.commercial_org_flag, a.isin_develop_org_flag, a.public_issue_flag, a.customize_issuer_id,
a.today_fin_flag, a.fin_udly_flag, a.marketmaker_amount, a.slo_udly_flag, a.today_slo_flag, a.exdividend_flag, a.fair_price2, a.chinasecurities_fair_price2,
a.reference_fa_code, a.trade_active_flag)})}]),
update([a.stock_status=column_conv(CHAR,utf8mb4_bin,length:1,NOT NULL,cast(inner_trim(2, cast(’ ', VARCHAR(1048576)), ‘2’), CHAR(1048576)))])
1 - output([a.inter_code], [a.business_date], [a.market_no], [a.stock_type], [a.asset_type], [a.report_code], [a.stock_name], [a.stock_fullname], [a.stock_spell],
[a.stock_status], [a.underwriter_id_list], [a.wx_listing_date], [a.listing_date], [a.frozen_code], [a.asset_relative_code], [a.match_relative_code], [a.issuer_id],
[a.trade_currency_no], [a.settle_currency_no], [a.uplimited_amount], [a.downlimited_amount], [a.buy_unit], [a.sale_unit], [a.amount_per_hand], [a.uplimited_ratio],
[a.downlimited_ratio], [a.transfer_mode], [a.stb_trans_status], [a.total_share], [a.outstanding_share], [a.non_turnover_share], [a.reference_price], [a.mmbz],
[a.board_type], [a.long_stop_flag], [a.marketprice_order_max], [a.marketprice_order_min], [a.issue_total_share], [a.publish_date], [a.gzstock_layer], [a.mktp_buy_amount_unit],
[a.mktp_sale_amount_unit], [a.no_profit_flag], [a.voting_right_diff_flag], [a.regist_system_flag], [a.vie_flag], [a.stock_stop_flag], [a.isin_code], [a.pe],
[a.non_public_issue_flag], [a.turnover_date], [a.online_estimated_ratio], [a.offline_estimate_ratio], [a.policy_org_flag], [a.commercial_org_flag], [a.isin_develop_org_flag],
[a.public_issue_flag], [a.customize_issuer_id], [a.today_fin_flag], [a.fin_udly_flag], [a.marketmaker_amount], [a.slo_udly_flag], [a.today_slo_flag], [a.exdividend_flag],
[a.fair_price2], [a.chinasecurities_fair_price2], [a.reference_fa_code], [a.trade_active_flag]), filter(nil), rowset=256
equal_conds([a.report_code = VIEW1.b.SecurityID]), other_conds(nil)
2 - output([VIEW1.b.SecurityID]), filter(nil), rowset=256
access([VIEW1.b.SecurityID])
3 - output([b.SecurityID]), filter(nil), rowset=256
access([b.SecurityID]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([b.SecurityID], [b.AUTO_HS_ID]), range(NULL,MAX ; LRZJHK,MIN), (LRZJHK,MAX ; MAX,MAX),
range_cond([b.SecurityID != cast(‘LRZJHK’, VARCHAR(1048576))])
4 - output([a.inter_code], [a.business_date], [a.market_no], [a.stock_type], [a.asset_type], [a.report_code], [a.stock_name], [a.stock_fullname], [a.stock_spell],
[a.stock_status], [a.underwriter_id_list], [a.wx_listing_date], [a.listing_date], [a.frozen_code], [a.asset_relative_code], [a.match_relative_code], [a.issuer_id],
[a.trade_currency_no], [a.settle_currency_no], [a.uplimited_amount], [a.downlimited_amount], [a.buy_unit], [a.sale_unit], [a.amount_per_hand], [a.uplimited_ratio],
[a.downlimited_ratio], [a.transfer_mode], [a.stb_trans_status], [a.total_share], [a.outstanding_share], [a.non_turnover_share], [a.reference_price], [a.mmbz],
[a.board_type], [a.long_stop_flag], [a.marketprice_order_max], [a.marketprice_order_min], [a.issue_total_share], [a.publish_date], [a.gzstock_layer], [a.mktp_buy_amount_unit],
[a.mktp_sale_amount_unit], [a.no_profit_flag], [a.voting_right_diff_flag], [a.regist_system_flag], [a.vie_flag], [a.stock_stop_flag], [a.isin_code], [a.pe],
[a.non_public_issue_flag], [a.turnover_date], [a.online_estimated_ratio], [a.offline_estimate_ratio], [a.policy_org_flag], [a.commercial_org_flag], [a.isin_develop_org_flag],
[a.public_issue_flag], [a.customize_issuer_id], [a.today_fin_flag], [a.fin_udly_flag], [a.marketmaker_amount], [a.slo_udly_flag], [a.today_slo_flag], [a.exdividend_flag],
[a.fair_price2], [a.chinasecurities_fair_price2], [a.reference_fa_code], [a.trade_active_flag]), filter(nil), rowset=256
equal_conds([a.inter_code = VIEW2.c.asset_relative_code]), other_conds(nil)
merge_directions([ASC])
5 - output([a.inter_code], [a.business_date], [a.market_no], [a.stock_type], [a.asset_type], [a.report_code], [a.stock_name], [a.stock_fullname], [a.stock_spell],
[a.stock_status], [a.underwriter_id_list], [a.wx_listing_date], [a.listing_date], [a.frozen_code], [a.asset_relative_code], [a.match_relative_code], [a.issuer_id],
[a.trade_currency_no], [a.settle_currency_no], [a.uplimited_amount], [a.downlimited_amount], [a.buy_unit], [a.sale_unit], [a.amount_per_hand], [a.uplimited_ratio],
[a.downlimited_ratio], [a.transfer_mode], [a.stb_trans_status], [a.total_share], [a.outstanding_share], [a.non_turnover_share], [a.reference_price], [a.mmbz],
[a.board_type], [a.long_stop_flag], [a.marketprice_order_max], [a.marketprice_order_min], [a.issue_total_share], [a.publish_date], [a.gzstock_layer], [a.mktp_buy_amount_unit],
[a.mktp_sale_amount_unit], [a.no_profit_flag], [a.voting_right_diff_flag], [a.regist_system_flag], [a.vie_flag], [a.stock_stop_flag], [a.isin_code], [a.pe],
[a.non_public_issue_flag], [a.turnover_date], [a.online_estimated_ratio], [a.offline_estimate_ratio], [a.policy_org_flag], [a.commercial_org_flag], [a.isin_develop_org_flag],
[a.public_issue_flag], [a.customize_issuer_id], [a.today_fin_flag], [a.fin_udly_flag], [a.marketmaker_amount], [a.slo_udly_flag], [a.today_slo_flag], [a.exdividend_flag],
[a.fair_price2], [a.chinasecurities_fair_price2], [a.reference_fa_code], [a.trade_active_flag]), filter(nil), rowset=256
conds([c.business_date > a.publish_date]), nl_params_(nil), use_batch=false
6 - output([a.inter_code], [a.stock_status], [a.business_date], [a.market_no], [a.stock_type], [a.asset_type], [a.report_code], [a.stock_name], [a.stock_fullname],
[a.stock_spell], [a.underwriter_id_list], [a.wx_listing_date], [a.listing_date], [a.frozen_code], [a.asset_relative_code], [a.match_relative_code], [a.issuer_id],
[a.trade_currency_no], [a.settle_currency_no], [a.uplimited_amount], [a.downlimited_amount], [a.buy_unit], [a.sale_unit], [a.amount_per_hand], [a.uplimited_ratio],
[a.downlimited_ratio], [a.transfer_mode], [a.stb_trans_status], [a.total_share], [a.outstanding_share], [a.non_turnover_share], [a.reference_price], [a.mmbz],
[a.board_type], [a.long_stop_flag], [a.marketprice_order_max], [a.marketprice_order_min], [a.issue_total_share], [a.publish_date], [a.gzstock_layer], [a.mktp_buy_amount_unit],
[a.mktp_sale_amount_unit], [a.no_profit_flag], [a.voting_right_diff_flag], [a.regist_system_flag], [a.vie_flag], [a.stock_stop_flag], [a.isin_code], [a.pe],
[a.non_public_issue_flag], [a.turnover_date], [a.online_estimated_ratio], [a.offline_estimate_ratio], [a.policy_org_flag], [a.commercial_org_flag], [a.isin_develop_org_flag],
[a.public_issue_flag], [a.customize_issuer_id], [a.today_fin_flag], [a.fin_udly_flag], [a.marketmaker_amount], [a.slo_udly_flag], [a.today_slo_flag], [a.exdividend_flag],
[a.fair_price2], [a.chinasecurities_fair_price2], [a.reference_fa_code], [a.trade_active_flag]), filter([a.stock_type = 1], [a.market_no = 1], [a.report_code
!= cast(‘LRZJHK’, VARCHAR(1048576))]), rowset=256
access([a.inter_code], [a.stock_status], [a.business_date], [a.market_no], [a.stock_type], [a.asset_type], [a.report_code], [a.stock_name], [a.stock_fullname],
[a.stock_spell], [a.underwriter_id_list], [a.wx_listing_date], [a.listing_date], [a.frozen_code], [a.asset_relative_code], [a.match_relative_code], [a.issuer_id],
[a.trade_currency_no], [a.settle_currency_no], [a.uplimited_amount], [a.downlimited_amount], [a.buy_unit], [a.sale_unit], [a.amount_per_hand], [a.uplimited_ratio],
[a.downlimited_ratio], [a.transfer_mode], [a.stb_trans_status], [a.total_share], [a.outstanding_share], [a.non_turnover_share], [a.reference_price], [a.mmbz],
[a.board_type], [a.long_stop_flag], [a.marketprice_order_max], [a.marketprice_order_min], [a.issue_total_share], [a.publish_date], [a.gzstock_layer], [a.mktp_buy_amount_unit],
[a.mktp_sale_amount_unit], [a.no_profit_flag], [a.voting_right_diff_flag], [a.regist_system_flag], [a.vie_flag], [a.stock_stop_flag], [a.isin_code], [a.pe],
[a.non_public_issue_flag], [a.turnover_date], [a.online_estimated_ratio], [a.offline_estimate_ratio], [a.policy_org_flag], [a.commercial_org_flag], [a.isin_develop_org_flag],
[a.public_issue_flag], [a.customize_issuer_id], [a.today_fin_flag], [a.fin_udly_flag], [a.marketmaker_amount], [a.slo_udly_flag], [a.today_slo_flag], [a.exdividend_flag],
[a.fair_price2], [a.chinasecurities_fair_price2], [a.reference_fa_code], [a.trade_active_flag]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false],
range_key([a.inter_code]), range(MIN ; MAX)always true
7 - output([c.business_date]), filter(nil), rowset=256
8 - output([c.business_date]), filter(nil), rowset=256
access([c.business_date]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([c.market_no]), range[1 ; 1],
range_cond([1 = c.market_no])
9 - output([VIEW2.c.asset_relative_code]), filter(nil), rowset=256
sort_keys([VIEW2.c.asset_relative_code, ASC])
10 - output([VIEW2.c.asset_relative_code]), filter(nil), rowset=256
access([VIEW2.c.asset_relative_code])
11 - output([c.asset_relative_code]), filter([c.stock_type = 33]), rowset=256
access([c.inter_code], [c.asset_relative_code], [c.stock_type]), partitions(p0)
is_index_back=true, is_global_index=false, filter_before_indexback[false],
range_key([c.market_no], [c.report_code], [c.shadow_pk_0]), range(1,MIN,MIN ; 1,MAX,MAX),
range_cond([c.market_no = 1])

[秃蛙]

一个已知的缺陷，ob424版本修复的，可以尝试调大租户内存是否能正常，如果不行建议升级到最新的ob425版本。

[皇甫侯]

具体原因和触发场景能解释下吗。。看执行计划计划没走NLJ，a表看样子也是全表扫描加不加a.inter_code > 0 其实也都一样

[秃蛙]

merge join 接自动内存管理的bug，在Left anti join场景下 低概率出现，l_store的内存使用超过了自动内存给出t_mem_bound限制，造成计算出来的r_mem_bound是负数。

[AntTech_DZYC6F]

老师 具体的修复版本号方便给一下吗

[AntTech_DZYC6F]

加了这个条件，执行计划不一样

|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |UPDATE | |1 |584 |
|1 |└─HASH ANTI JOIN | |1 |558 |
|2 | ├─NESTED-LOOP ANTI JOIN | |1 |552 |
|3 | │ ├─NESTED-LOOP JOIN | |1 |527 |
|4 | │ │ ├─TABLE RANGE SCAN |a |3 |524 |
|5 | │ │ └─MATERIAL | |1 |3 |
|6 | │ │ └─TABLE GET |c |1 |3 |
|7 | │ └─SUBPLAN SCAN |VIEW2 |1 |25 |
|8 | │ └─TABLE RANGE SCAN |c(qy_idx_totherasset) |1 |25 |
|9 | └─SUBPLAN SCAN |VIEW1 |1 |6 |
|10| └─TABLE RANGE SCAN |b(qy_idx_tmktdt00_md002)|1 |6 |

Outputs & filters:

[秃蛙]

ob424bp0，建议直接升级到最新的ob425bp6.