explain SQL 报错 4000

问题描述

在 OceanBase 数据库中执行 update 向量形式的子查询时，使用 explain sql 命令解析语句时报错 4000。

示例如下。

1. 创建测试表。

   obclient> CREATE TABLE t1(c1 int, c2 int);
   Query OK, 0 rows affected (0.27 sec)
   

   obclient> CREATE TABLE t2(c1 int, c2 int);
   Query OK, 0 rows affected (0.23 sec)
   

   obclient> CREATE TABLE t3(c1 int, c2 int);
   Query OK, 0 rows affected (0.29 sec)
   

2. 执行更新语句。

   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. 更新语句一执行计划。

      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. 更新语句二执行计划。

      explain extended update t1 set (c1,c2) = (select 1, (select 1 FROM t3 where t2.c2=t3.c2) from t2);
      

      输出结果如下。

      | ===============================================
      |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 语句，分别执行，以避免该错误。