--- title: explain SQL 报错 4000-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 explain SQL 报错 4000相关的常见问题和使用技巧，帮助您快速解决 explain SQL 报错 4000的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # explain SQL 报错 4000 更新时间：2024-01-22 01:56 适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x 内容类型：Troubleshoot ## 问题描述 在 OceanBase 数据库中执行 update 向量形式的子查询时，使用 explain sql 命令解析语句时报错 4000。 示例如下。 1. 创建测试表。 ```shell obclient> CREATE TABLE t1(c1 int, c2 int); Query OK, 0 rows affected (0.27 sec) ``` ```shell obclient> CREATE TABLE t2(c1 int, c2 int); Query OK, 0 rows affected (0.23 sec) ``` ```shell obclient> CREATE TABLE t3(c1 int, c2 int); Query OK, 0 rows affected (0.29 sec) ``` 2. 执行更新语句。 ```shell obclient > update t1 set (c1,c2) = (select 1, (select 1 FROM t3 where t2.c2=t3.c2) + 1 from t2); ORA-00600: internal error code, arguments: -4000, Common error ``` 3. 展示更新语句的执行计划。 1. 更新语句一执行计划。 ```shell obclient> explain extended update t1 set (c1,c2) = (select 1, (select 1 FROM t3 where t2.c2=t3.c2) + 1 from t2); ORA-00600: internal error code, arguments: -4000, Common error ``` 2. 更新语句二执行计划。 ```shell explain extended update t1 set (c1,c2) = (select 1, (select 1 FROM t3 where t2.c2=t3.c2) from t2); ``` 输出结果如下。 ```shell | =============================================== |ID|OPERATOR |NAME|EST. ROWS|COST | ----------------------------------------------- |0 |UPDATE | |100000 |5586933762| |1 | SUBPLAN FILTER | |100000 |5586833762| |2 | TABLE SCAN |T1 |100000 |38681 | |3 | SUBPLAN FILTER| |100000 |4080497628| |4 | TABLE SCAN |T2 |100000 |38681 | |5 | TABLE SCAN |T3 |990 |40790 | =============================================== Outputs & filters ------------------------------------- 0 - output(nil), filter(nil), table_columns([{T1: ({T1: (T1.__pk_increment(0x7fd81fff1d70), T1.C1(0x7fd81ff4e7f0), T1.C2(0x7fd81ff4eaf0))})}]), update([T1.C1(0x7fd81ff4e7f0)=column_conv(NUMBER,PS:(38,0),NULL,remove_const(1)(0x7fd81ffdfcd0))(0x7fd81ffe0a20)], [T1.C2(0x7fd81ff4eaf0)=column_conv(NUMBER,PS:(38,0),NULL,subquery(1)(0x7fd81ff95a60))(0x7fd81ffe8070)]) 1 - output([T1.__pk_increment(0x7fd81fff1d70)], [T1.C1(0x7fd81ff4e7f0)], [T1.C2(0x7fd81ff4eaf0)], [column_conv(NUMBER,PS:(38,0),NULL,remove_const(1)(0x7fd81ffdfcd0))(0x7fd81ffe0a20)], [column_conv(NUMBER,PS:(38,0),NULL,subquery(1)(0x7fd81ff95a60))(0x7fd81ffe8070)]), filter(nil), exec_params_(nil), onetime_exprs_(nil), init_plan_idxs_([1]) 2 - output([T1.C1(0x7fd81ff4e7f0)], [T1.C2(0x7fd81ff4eaf0)], [T1.__pk_increment(0x7fd81fff1d70)]), filter(nil), access([T1.C1(0x7fd81ff4e7f0)], [T1.C2(0x7fd81ff4eaf0)], [T1.__pk_increment(0x7fd81fff1d70)]), partitions(p0), is_index_back=false, range_key([T1.__pk_increment(0x7fd81fff1d70)]), range(MIN ; MAX)always true 3 - output([remove_const(1)(0x7fd81ffdfcd0)], [subquery(1)(0x7fd81ff95a60)]), filter(nil), exec_params_([T2.C2(0x7fd81ffdd170)]), onetime_exprs_(nil), init_plan_idxs_(nil) 4 - output([T2.C2(0x7fd81ffdd170)]), filter(nil), access([T2.C2(0x7fd81ffdd170)]), partitions(p0), is_index_back=false, range_key([T2.__pk_increment(0x7fd82008ef40)]), range(MIN ; MAX)always true 5 - output([remove_const(1)(0x7fd8200cfbc0)]), filter([? = T3.C2(0x7fd81ffdd460)(0x7fd81ffdca50)]), access([T3.C2(0x7fd81ffdd460)]), partitions(p0), is_index_back=false, filter_before_indexback[false], range_key([T3.__pk_increment(0x7fd8200ba480)]), range(MIN ; MAX)always true Used Hint ------------------------------------- /*+ */ Outline Data ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"UPD$1" "SYS.T1"@"UPD$1") FULL(@"SEL$1" "SYS.T2"@"SEL$1") FULL(@"SEL$2" "SYS.T3"@"SEL$2") END_OUTLINE_DATA */ Plan Type ------------------------------------- LOCAL Optimization Info ------------------------------------- T1:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[T1] T2:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[T2] T3:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:989, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[T3] Parameters ------------------------------------- ``` ## 适用版本 OceanBase 数据库 V2.x 和 V3.x 版本。 ## 问题原因 从上边示例中看，subquery(1) 的地址在 3 号算子和 0 号算子是一样的，计划生成的不对。理论上两个 subquery 是不一样的，3 号算子应该是内层的子查询，0 号算子应该是外层的子查询，由于地址分配混乱导致子查询的地址不正确。该问题是由执行计划生成的 bug 引起的。 ## 解决方法 升级至问题已修复版本。目前已修复的版本包括： - OceanBase 数据库企业版 V2.2.77 BP19 及后续版本、V3.1.2 BP12 及之后版本、V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 及之后版本、V3.2.4 BP5 (oceanbase-3.2.4.5-105000012023081513) 及之后版本。 - OceanBase 数据库社区版 V3.1.5 CE (oceanbase-ce-3.1.5-100000252023041721) 及之后版本。 ## 临时修改方案 将 update 拆分为两个单独的 update 语句，分别执行，以避免该错误。 上一篇 [Oracle 模式下开启 PS 和 cursor，SQL Audit 记录的 PARAMS_VALUE 是空的](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000232770) 下一篇 [如何排查表达式约束导致的计划不命中](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006346773) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线