query range 抽取报错 4016

问题描述

OceanBase 数据库 query range 抽取报错 4016。

复现如下:

1. 创建表

   obclient> create table t1(c1 int,c2 int,c3 int,c4 number(10,0));
   Query OK, 0 rows affected (0.033 sec)
   

2. 插入数据

   obclient > insert into t1 values(1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4),(5,5,5,5),(6,6,6,6);
   Query OK, 6 rows affected (0.010 sec)
   Records: 6  Duplicates: 0  Warnings: 0
   

3. 在两个列上创建索引

   obclient > create index idx on t1(c1,c2);
   Query OK, 0 rows affected (0.527 sec)
   

4. 展示执行计划

   obclient > explain extended select * From t1 a Where a.c1 In (1,2) And a.c2 In (3,4) And (a.c2, a.c3) > (5,6);
   ORA-00600: internal error code, arguments: -4016, Internal error
   

5. 删除索引

   drop index idx;
   Query OK, 0 rows affected (0.023 sec)
   

6. 在一个列上创建索引

   obclient > create index idx on t1(c1);
   Query OK, 0 rows affected (0.426 sec)
   

7. 展示执行计划

   obclient > explain extended Select * From t1 a Where a.c1 In (1,2) And a.c2 In (3,4) And (a.c2, a.c3) > (5,6);
   

   输出结果如下:

   | =====================================
   |ID|OPERATOR  |NAME  |EST. ROWS|COST|
   -------------------------------------
   |0 |TABLE SCAN|A(IDX)|1        |100 |
   =====================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([A.C1(0x7fb2b3d556c0)], [A.C2(0x7fb2b3d571e0)], [A.C3(0x7fb2b3d59730)]), filter([(A.C2(0x7fb2b3d571e0), A.C3(0x7fb2b3d59730))(0x7fb2b3d57670) > (5, 6)(0x7fb2b3d58370)(0x7fb2b3d58f90)], [A.C2(0x7fb2b3d571e0) IN (3, 4)(0x7fb2b3d56540)(0x7fb2b3d55b50)]),
         access([A.C1(0x7fb2b3d556c0)], [A.C2(0x7fb2b3d571e0)], [A.C3(0x7fb2b3d59730)]), partitions(p0),
         is_index_back=true, filter_before_indexback[false,false],
         range_key([A.C1(0x7fb2b3d556c0)], [A.__pk_increment(0x7fb2b3da5c60)]), range(1,MIN ; 1,MAX), (2,MIN ; 2,MAX),
         range_cond([A.C1(0x7fb2b3d556c0) IN (1, 2)(0x7fb2b3d54a20)(0x7fb2b3d54030)])
   

8. 将查询语句中 IN 表达式拆分成 OR 表达式

   explain extended select * from t1 a where ((a.c1=1) or (a.c1=2)) and ((a.c2=3) or (a.c2=4)) and ((a.c2,a
   .c3) > (5,6));
   

   输出结果如下:

   | ===================================
   |ID|OPERATOR  |NAME|EST. ROWS|COST|
   -----------------------------------
   |0 |TABLE SCAN|A   |1        |46  |
   ===================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([A.C1(0x7fb322156440)], [A.C2(0x7fb3221598d0)], [A.C3(0x7fb32215cd60)], [A.C4(0x7fb32215d290)]), filter([(A.C2(0x7fb3221598d0), A.C3(0x7fb32215cd60))(0x7fb32215aca0) > (5, 6)(0x7fb32215b9a0)(0x7fb32215c5c0)], [A.C1(0x7fb322156440) = 1(0x7fb322155010) OR A.C1(0x7fb322156440) = 2(0x7fb322155ca0)(0x7fb322154380)], [A.C2(0x7fb3221598d0) = 3(0x7fb3221584a0) OR A.C2(0x7fb3221598d0) = 4(0x7fb322159130)(0x7fb322157810)]),
         access([A.C1(0x7fb322156440)], [A.C2(0x7fb3221598d0)], [A.C3(0x7fb32215cd60)], [A.C4(0x7fb32215d290)]), partitions(p0),
         is_index_back=false, filter_before_indexback[false,false,false],
         range_key([A.__pk_increment(0x7fb3221aa020)]), range(MIN ; MAX)always true
   

9. 将向量表达式拆分成等价的普通表达式

   explain extended select * from t1 a where (a.c1 = 1 or a.c1 = 2) and (a.c2 = 3 or a.c2 = 4) and (a.c2,a.
   c3) > (5,6);
   

   输出结果如下:

   | ===================================
   |ID|OPERATOR  |NAME|EST. ROWS|COST|
   -----------------------------------
   |0 |TABLE SCAN|A   |1        |46  |
   ===================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([A.C1(0x7fb322156440)], [A.C2(0x7fb3221598d0)], [A.C3(0x7fb32215cd60)], [A.C4(0x7fb32215d290)]), filter([(A.C2(0x7fb3221598d0), A.C3(0x7fb32215cd60))(0x7fb32215aca0) > (5, 6)(0x7fb32215b9a0)(0x7fb32215c5c0)], [A.C1(0x7fb322156440) = 1(0x7fb322155010) OR A.C1(0x7fb322156440) = 2(0x7fb322155ca0)(0x7fb322154380)], [A.C2(0x7fb3221598d0) = 3(0x7fb3221584a0) OR A.C2(0x7fb3221598d0) = 4(0x7fb322159130)(0x7fb322157810)]),
         access([A.C1(0x7fb322156440)], [A.C2(0x7fb3221598d0)], [A.C3(0x7fb32215cd60)], [A.C4(0x7fb32215d290)]), partitions(p0),
         is_index_back=false, filter_before_indexback[false,false,false],
         range_key([A.__pk_increment(0x7fb3221aa020)]), range(MIN ; MAX)always true
   

适用版本

OceanBase 数据库 V3.2.3 BP4 (oceanbase-3.2.3.1-20220805104621) 到 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 之间版本。

问题原因

同时出现两个及以上的 in 表达式和至少一个向量表达式且命中索引。

例如上面的 (c2,c3)>(5,6),c3 不是索引列，只需生成 c2 上的 keypart 即可，这样的 keypart 不是向量形式的，contain_row_ 应该为 false，但是目前仍会设为 true。range graph 在 contain_row_ 为 true 的时候，目前是没有处理 in keypart 的，当同时出现两个 in keypart 的时候，就会进入错误的路径，导致报错。

解决方法

• 升级至问题已修复版本, 目前已修复的版本为 OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 及之后版本。

• 临时解决方法:

  • 改索引，不要同时在 c1 和 c2 上建索引。

  • 将 in 表达式拆成 or 表达式。

  • 将向量表达式拆成等价的普通表达式。