首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
针对大 in 谓词 query range 抽取的优化
更新时间：2023-12-25 05:31
分享
适用版本：V3.2.x、V4.1.x、V4.2.x内容类型：Troubleshoot
当 SQL 中存在 in 谓词，并且执行计划选择了 in 谓词相关的索引，进行了 query range 抽取，可能出现查询结果错的问题。
IN 优化引入的正确性问题的规避方式
遇到 in 谓词 query range 抽取相关的正确性问题时，可以尝试通过以下方法进行规避。
将 in 表达式改成等价的 or 表达式，例如
c1 in ('1','2') => (c1 = '1' or c1 = '2')。通过 hint 禁止走包含 in 表达式所在列的索引，例如
select /*+full(t1)*/...。在 in 表达式后面加个 is true（适用于命中主键索引的场景），例如
c1 in ('1','2') is true。通过
_enable_in_range_optimization的隐藏配置项来决定是否打开 in 优化，包含该配置项的版本及默认行为如下。包含 IN 优化的版本 包含 IN 优化开关的版本 IN 优化默认值 V4.2.0 V4.2.0 打开 V4.1.0 V4.1.0 BP2 打开 V3.2.4 V3.2.4 BP4 关闭 V3.2.3 V3.2.3 BP8 Hotfix5 关闭
其中，方法一适合 in 中参数比较少的场景；方法二可通过绑 hint/改索引做到，这种方式用得最多，但是不适合 in 表达式所在列是主键的场景；方法三可应用于任意场景，但是做法比较取巧，一般作为方法二的补充。
说明
本文所有的 case 中存在 ci 时，均可认为存在(c1,c2,c3,..., ci) 的索引或主键，例如 case:(c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8)，表示含有联合主键/索引(c1,c2,c3,c4)。
IN 优化引入的正确性问题和解决方法
场景 01：SQL 中含有 in 表达式导致正确性问题
问题现象
当 SQL 中含有 (c1 in (?,?) or c1 in (?,?)) and c2 特证的语句，且出现的不同列都是联合主键/索引时，会出现正确性问题。
例如 (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8)，表示含有联合主键/索引 (c1,c2,c3,c4)。
示例。
创建表与索引
obclient [SYS]> create table t1(c1 varchar2(50), c2 varchar2(50)); Query OK, 0 rows affected (0.029 sec)obclient [SYS]> create index idx on t1(c1,c2); Query OK, 0 rows affected (0.448 sec)存在正确性问题的 SQL。
obclient [SYS]> select /*+index(t1 idx)*/* from t1 where (c1 in ('AD', 'AC') or c1 in ('AD', 'ZT')) and c2 = '01'; Empty set (0.006 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr select /*+index(t1 idx)*/* from t1 where (c1 in ('AD', 'AC') or c1 in ('AD', 'ZT')) and c2 = '01';输出结果如下:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------- |0 |TABLE SCAN|T1(IDX)|2970 |1149| ====================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2]), filter(nil), access([T1.C1], [T1.C2]), partitions(p0), is_index_back=false, range_key([T1.C1], [T1.C2], [T1.__pk_increment]), range(AD,MIN,MIN ; AD,MAX,MAX), (AC,MIN,MIN ; AC,MAX,MAX), (ZT,MIN,MIN ; ZT,MAX,MAX), range_cond([T1.C2 = ?], [T1.C1 IN (?, ?) OR T1.C1 IN (?, ?)]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec)
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 版本、V3.2.4 BP4 (oceanbase-3.2.4.4-104000052023062021) 版本、V4.1.0 BP2 (oceanbase-4.1.0.1-102000042023061309) 版本。
规避方案
改写 SQL。
select * from t1 where ((c1 = 'AD' or c1 = 'AC') or (c1 = 'AD' or c1 = 'ZT')) and c2 = '01';添加 FULL Hint 走主表。
select /*+full(t1)*/* from t1 where (c1 in ('AD', 'AC') or c1 in ('AD', 'ZT')) and c2 = '01';in 表达式后加 is true。
select * from t1 where (c1 in ('AD', 'AC') is true or c1 in ('AD', 'ZT') is true) and c2 = '01';
场景 02：SQL 中含有 in 表达式导致正确性问题
问题现象
当 SQL 中含有 (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8) 特征，且满足以下三个条件时，会出现正确性问题。
c1,c2,c3,c4 四个索引列。
c1，c2 必须是 or 的普通表达式，c3 是普通或者 in 表达式，c4 必须是 in 表达式。
四个列的表达式以 and 连接。
示例。
创建表与索引
obclient [SYS]> create table t1(c1 int, c2 int, c3 int, c4 int, c5 int); Query OK, 0 rows affected (0.027 sec)obclient [SYS]> create index idx on t1(c1,c2,c3,c4); Query OK, 0 rows affected (0.438 sec)存在正确性问题的 SQL。
obclient [SYS]> select /*+index (t1 idx)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8); Empty set (0.007 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr select /*+index (t1 idx)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8);输出结果如下:
============================================= |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| --------------------------------------------- |0 |TABLE SCAN|t1(idx)|1 |5 | ============================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1(0x7f377403db10)], [t1.c2(0x7f3774040a60)], [t1.c3(0x7f3774042710)], [t1.c4(0x7f3774044170)], [t1.c5(0x7f37740447d0)]), filter(nil), rowset=256 access([t1.__pk_increment(0x7f3774045510)], [t1.c1(0x7f377403db10)], [t1.c2(0x7f3774040a60)], [t1.c3(0x7f3774042710)], [t1.c4(0x7f3774044170)], [t1.c5(0x7f37740447d0)]), partitions(p0) is_index_back=true, is_global_index=false, range_key([t1.c1(0x7f377403db10)], [t1.c2(0x7f3774040a60)], [t1.c3(0x7f3774042710)], [t1.c4(0x7f3774044170)], [t1.__pk_increment(0x7f3774045510)]), range(1,3,5,7,MIN ; 1,3,5,7,MAX), (1,3,5,8,MIN ; 1,3,5,8,MAX), (1,4,5,7,MIN ; 1,4,5,7,MAX), (1,4,5,8,MIN ; 1,4,5,8,MAX), (2,3,5,7,MIN ; 2,3,5,7,MAX), (2,3,5,8,MIN ; 2,3,5,8,MAX), range_cond([t1.c3(0x7f3774042710) = 5(0x7f3774042060)], [t1.c1(0x7f377403db10) = 1(0x7f377403c830) OR t1.c1(0x7f377403db10) = 2(0x7f377403d3e0)(0x7f377403bc80)], [t1.c2(0x7f3774040a60) = 3(0x7f377403f780) OR t1.c2(0x7f3774040a60) = 4(0x7f3774040330)(0x7f377403ebd0)], [t1.c4(0x7f3774044170) IN (7, 8)(0x7f3774047670)(0x7f3774042c70)])
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 版本、V3.2.4 BP4 (oceanbase-3.2.4.4-104000052023062021) 版本、V4.1.0 BP2 (oceanbase-4.1.0.1-102000042023061309) 版本。
规避方案
改写 SQL。
select * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and (c4 = 7 or c4 = 8);添加 FULL Hint 走主表。
select /*+full (t1)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8);in 表达式后加 is true。
select /*+full (t1)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8) is true;
场景 03：SQL 中含有 in 表达式导致正确性问题
问题现象
当 SQL 中含有 in 表达式和普通表达式做完 or 合并后，in 中只剩一个参数，如场景 02 中案例做完 or 合并后变为 c1 in (7) or c1 < 3 or c1 > 10 时，会出现正确性问题。
示例。
创建主键表。
obclient [SYS]> CREATE TABLE t1 ( pk int primary key); Query OK, 0 rows affected (0.021 sec)插入语句并提交。
obclient [SYS]> INSERT INTO t1 VALUES (1),(2); Query OK, 2 rows affected (0.004 sec)obclient [SYS]> commit; Query OK, 0 rows affected (0.001 sec)关闭全局 Plan Cache。
obclient [SYS]> set @@ob_enable_plan_cache = 0;存在正确性问题的 SQL。
obclient [SYS]> SELECT * FROM t1 WHERE PK IN (7,10) OR pk < 3 or pk > 10; Empty set (0.005 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr SELECT * FROM t1 WHERE PK IN (7,10) OR pk < 3 or pk > 10;输出结果如下:
================================================ |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| ------------------------------------------------ |0 |TABLE GET|t1 |4 |2 | -- 这个不是Table Get的 ================================================ Outputs & filters: ------------------------------------- 0 - output([t1.pk]), filter(nil), rowset=256 access([t1.pk]), partitions(p0) is_index_back=false, is_global_index=false, range_key([t1.pk]), range[7 ; 7], (NULL ; 3), [10 ; MAX), range_cond([(T_OP_OR, t1.pk IN (7, 10), t1.pk < 3, t1.pk > 10)])
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 版本、V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 版本、V4.1.0 BP2 (oceanbase-4.1.0.1-102000042023061309) 版本。
规避方案
改写 SQL。
SELECT * FROM t1 WHERE (PK = 7 or pk = 10) OR pk < 3 or pk > 10;in 表达式后加 is true。
SELECT * FROM t1 WHERE PK IN (7,10) is true OR pk < 3 or pk > 10;注意
PK 是主键列，走主表的方法无效。
场景 04：SQL 中含有 in 表达式导致正确性问题
问题现象
当 SQL 中含有 (c1 = x or c1 = ...) and c2 in (...) and c3 ... 特征时，会出现正确性问题。
示例。
创建表与索引。
obclient [SYS]> create table t1 (c1 int, c2 int, c3 int, c4 int); Query OK, 0 rows affected (0.31 sec)obclient [SYS]> create index idx1 on t1(c1,c2,c3); Query OK, 0 rows affected (0.418 sec)插入语句。
obclient [SYS]> insert into t1 values(2,4,7,2),(2,5,7,2),(2,4,7,3),(3,4,7,2),(3,5,7,2); Query OK, 2 rows affected (0.006 sec)存在正确性问题的 SQL。
obclient [SYS]> select /*+index(t1 idx1)*/ * from t1 where (c1 = 2 or c1 = 3) and c2 in (4,5,6) and c3 = 7;输出结果如下:
+------+------+------+------+ | C1 | C2 | C3 | C4 | +------+------+------+------+ | 2 | 4 | 7 | 2 | | 2 | 4 | 7 | 3 | | 2 | 4 | 7 | 2 | | 2 | 4 | 7 | 3 | | 2 | 5 | 7 | 2 | | 3 | 4 | 7 | 2 | | 2 | 5 | 7 | 2 | | 2 | 5 | 7 | 2 | | 3 | 4 | 7 | 2 | | 3 | 5 | 7 | 2 | | 3 | 4 | 7 | 2 | | 3 | 5 | 7 | 2 | +------+------+------+------+ 12 rows in set (0.004 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [SYS]> obclient [SYS]> explain extended_noaddr select /*+index(t1 idx1)*/ * from t1 where (c1 = 2 or c1 = 3) and c2 in (4,5,6) and c3 = 7;输出结果如下:
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ---------------------------------------- |0 |TABLE SCAN|T1(IDX1)|15001 |59050| ======================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4]), filter(nil), access([T1.C1], [T1.C2], [T1.C3], [T1.C4]), partitions(p0), is_index_back=true, range_key([T1.C1], [T1.C2], [T1.C3], [T1.__pk_increment]), range(2,4,7,MIN ; 2,4,7,MAX), (2,5,7,MIN ; 2,5,7,MAX), (2,6,7,MIN ; 2,6,7,MAX), (2,4,7,MIN ; 3,4,7,MAX), (2,5,7,MIN ; 3,5,7,MAX), (2,6,7,MIN ; 3,6,7,MAX), range_cond([T1.C3 = 7], [T1.C1 = 2 OR T1.C1 = 3], [T1.C2 IN (4, 5, 6)]) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec)
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521)、V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819)、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 、V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。
规避方案
改写 SQL。
select * from t1 where (c1 = 2 or c1 = 3) and (c2 = 4 or c2 = 5 or c2 = 6) and c3 = 7;添加 FULL Hint 走主表。
select /*+full(t1)*/* from t1 where (c1 = 2 or c1 = 3) and (c2 in (4,5,6) and c3 = 7;in 表达式后加 is true。
select * from t1 where (c1 = 2 or c1 = 3) and c2 in (4,5,6) is true and c3 = 7;
场景 05：SQL 中含有 in 表达式导致正确性问题
问题版本
当 SQL 中含有以下特性时，会出现正确性问题。
c1 = x and c2 = y .... and ci {>,>=,<,<=} z and [c_i+1 = m and c_i+2 = n ...] ... and cj in (?,?...) and cj+1 ...。
c1 = x and (c2 > 0 and c2 < -1) and c3 in (??)。
注意
在上述两个场景都会出现正确性问题。
示例。
创建表。
obclient [SYS]> create table t1(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, primary key(c1,c2,c3,c4,c5)); Query OK, 0 rows affected (0.39 sec)存在正确性问题的 SQL。
obclient [SYS]> select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) and c4 > 0; Empty set (0.005 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) and c4 > 0;输出结果如下:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|T1 |1 |48 | =================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4], [T1.C5], [T1.C6]), filter([T1.C3 IN (3, 4, 5)], [T1.C4 > 0]), access([T1.C1], [T1.C2], [T1.C3], [T1.C4], [T1.C5], [T1.C6]), partitions(p0), is_index_back=false, filter_before_indexback[false,false], range_key([T1.C1], [T1.C2], [T1.C3], [T1.C4], [T1.C5]), range(1,1,3,0,MAX ; 1,5,5,NULL,MIN), range_cond([T1.C1 = 1], [T1.C2 >= 1], [T1.C2 <= 5]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.007 sec)
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 版本。
规避方案
改写 SQL。
select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and (c3 = 3 or c3 = 4 or c3 =5) and c4 > 0;添加 FULL Hint 走主表。
select /*+full(t1)*/* from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) and c4 > 0;in 表达式后加 is true。
select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) is true and c4 > 0;
场景 06：SQL 中含有 in 表达式导致正确性问题
问题现象
当 SQL 中含有 (c1 IN (1,2) or (c1 IS NULL)) and c1 is null 特征时，会出现正确性问题。
示例。
创建表与索引
obclient [SYS]> create table t1(c1 int); Query OK, 0 rows affected (0.019 sec)obclient [SYS]> create index t1_c1_idx on t1(c1); Query OK, 0 rows affected (0.398 sec)存在正确性问题的 SQL。
SELECT * FROM T1 WHERE (c1 IN (1,2) or (c1 IS NULL)) and c1 is null\G; Empty set (0.006 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr SELECT * FROM T1 WHERE (c1 IN (1,2) or (c1 IS NULL)) and c1 is null\G;输出结果如下:
*************************** 1. row *************************** Query Plan: ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|T1(T1_C1_IDX)|1 |46 | ============================================ Outputs & filters: ------------------------------------- 0 - output([T1.C1]), filter(nil), access([T1.C1]), partitions(p0), is_index_back=false, range_key([T1.C1], [T1.__pk_increment]), range(NULL,MIN ; NULL,MAX), range_cond([T1.C1 IN (1, 2) OR (T_OP_IS, T1.C1, NULL, 0)], [(T_OP_IS, T1.C1, NULL, 0)])
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP6 Hotfix2 (oceanbase-3.2.3.3-106020042022120915) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 及之后版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本。
规避方案
改写 SQL。
SELECT/*+index(t1 t1_c1_idx)*/ * FROM T1 WHERE ((c1 = 1 or c1 = 2) or (c1 IS NULL)) and c1 is null;添加 FULL Hint 走主表。
SELECT/*+full(t1)*/ * FROM T1 WHERE (c1 IN (1,2) or (c1 IS NULL)) and c1 is null;in 表达式后加 is true。
SELECT/*+index(t1 t1_c1_idx)*/ * FROM T1 WHERE (c1 IN (1,2) is true or (c1 IS NULL)) and c1 is null;
场景 07：SQL 中含有 in 表达式导致正确性问题
当 SQL 中含有 c1 IN (?,?,?) AND c2 IN (?,?) OR c1 IN (?,?) AND c2 > ? 特征时，会出现正确性问题。
示例。
创建表。
obclient [mysql]> CREATE TABLE table10_hash_pk_parts_4 ( col_varchar_20 varchar(20), col_timestamp timestamp NULL DEFAULT NULL, col_bigint bigint, pk int, col_decimal_20_0 decimal(20,0), col_double double); Query OK, 0 rows affected (0.032 sec)插入数据。
obclient [mysql]> INSERT INTO table10_hash_pk_parts_4 VALUES ('', NULL, 0, NULL, 34, NULL) , (NULL, '2008-04-21 22:28:30.032585', NULL, -110, 0, 0) , (NULL, '2000-01-13 16:04:43.000020', NULL, 105, 9, -19699) , ('rdetnn', NULL, 2476, 1, 6, 9) , (NULL, '2007-06-21 07:04:43.008773', 24463, -127, -107, 0) , ('p', NULL, NULL, -23078, 2, 15); Query OK, 2 rows affected (0.004 sec) Records: 6 Duplicates: 0 Warnings: 0创建索引。
obclient [mysql]> create index in_expr_idx8 on table10_hash_pk_parts_4 (col_bigint,col_decimal_20_0,col_varchar_20,col_timestamp); Query OK, 0 rows affected (0.446 sec)存在正确性问题的 SQL。
obclient [mysql]> SELECT /*+index(table10_hash_pk_parts_4 in_expr_idx8)*/ * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc;输出结果如下：
+----------------+---------------+------------+------+------------------+------------+ | col_varchar_20 | col_timestamp | col_bigint | pk | col_decimal_20_0 | col_double | +----------------+---------------+------------+------+------------------+------------+ | | NULL | 0 | NULL | 34 | NULL | +----------------+---------------+------------+------+------------------+------------+ 1 row in set (0.008 sec)展示存在正确性问题的 SQL 的执行计划。
obclient [mysql]> explain extended_noaddr SELECT /*+index(table10_hash_pk_parts_4 in_expr_idx8)*/ * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc;输出结果如下:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------------------------------- |0 |SORT | |1 |92 | |1 | TABLE SCAN|table10_hash_pk_parts_4(in_expr_idx8)|1 |92 | ===================================================================== Outputs & filters: ------------------------------------- 0 - output([table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.pk], [table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_double]), filter(nil), sort_keys([table10_hash_pk_parts_4.pk, DESC]) 1 - output([table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.pk], [table10_hash_pk_parts_4.col_double]), filter([table10_hash_pk_parts_4.col_decimal_20_0 IN (?, ?) AND table10_hash_pk_parts_4.col_bigint = 0 OR (table10_hash_pk_parts_4.col_bigint, table10_hash_pk_parts_4.col_bigint) <= (4, 4)]), access([table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.pk], [table10_hash_pk_parts_4.col_double]), partitions(p0), is_index_back=true, filter_before_indexback[true], range_key([table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.__pk_increment]), range(NULL,MAX,MAX,MAX,MAX ; 4,MAX,MAX,MAX,MAX) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.012 sec)
问题的风险及影响
正确性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP6 Hotfix2 (oceanbase-3.2.3.3-106020042022120915) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 及之后版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。
规避方案
改写 SQL。
SELECT /*+index(table10_hash_pk_parts_4 in_expr_idx8)*/ * FROM table10_hash_pk_parts_4 WHERE (col_decimal_20_0 = 34 or col_decimal_20_0 = 3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc;添加 FULL Hint 走主表。
SELECT /*+full(table10_hash_pk_parts_4)*/ * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc;in 表达式后加 is true。
SELECT * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) is true AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc;
场景 08：SQL 中含有 in 表达式导致稳定性问题，可能引发 500 租户 CommonArray 内存泄漏
问题现象
当 SQL 中含有 c1 in (??) and c1 in (??) 特征时，可能引发 500 租户 CommonArray 内存泄漏，堆栈中会包含 oceanbase::sql::ObKeyPart::merge_two_in_keys。
示例。
创建表。
obclient [SYS]> create table t1(C1 int primary key); Query OK, 0 rows affected (0.030 sec)存在稳定性问题的 SQL。
obclient [SYS]> select * from t1 where c1 in (1,2,3) and c1 in (2,3,4); Empty set (0.004 sec)展示存在稳定性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr select * from t1 where c1 in (1,2,3) and c1 in (2,3,4);输出结果如下:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------- |0 |TABLE GET|T1 |2 |92 | ================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1]), filter(nil), access([T1.C1]), partitions(p0), is_index_back=false, range_key([T1.C1]), range[2 ; 2], [3 ; 3], range_cond([T1.C1 IN (1, 2, 3)], [T1.C1 IN (2, 3, 4)]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec)
问题的风险及影响
稳定性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP7 Hotfix2 (oceanbase-3.2.3.3-107020062023022810) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 Hotfix3 (oceanbase-3.2.3.3-107030052023031712) 及之后版本、V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 版本。
规避方案
改写 SQL。
select * from t1 where (c1 = 1 or c1 = 2 or c1 = 3) and (c1 = 2 or c1 = 3 or c1 = 4);in 表达式后加 is true。
select * from t1 where c1 in (1,2,3) is true and c1 in (2,3,4) is true;注意
c1 是主键，Hint 不可用。
场景 09：SQL 中含有 in 表达式导致稳定性问题，可能引发 sanity 版本 core
问题现象
当 SQL 中含有 (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2 特征时，可能引发 sanity 版本 core，堆栈中会包含 ObQueryRange::and_first_in_key 和 ObQueryRange::ObSearchState::tailor_final_range。
示例。
创建表与索引
obclient [SYS]> create table t1(c1 int, c2 int, c3 int); Query OK, 0 rows affected (0.029 sec)obclient [SYS]> create index idx on t1(c1,c2); Query OK, 0 rows affected (0.439 sec)存在稳定性问题的 SQL。
obclient [SYS]> select * from t1 where (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2; Empty set (0.007 sec)展示存在稳定性问题的 SQL 的执行计划。
obclient [SYS]> explain extended_noaddr select * from t1 where (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2;输出结果如下:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------- |0 |TABLE SCAN|T1(IDX)|10 |502 | ====================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t1.c3]), filter([(t1.c1, t1.c2) IN ((1, 1), (2, 2))], [t1.c2 = 2]), access([t1.c1], [t1.c2], [t1.c3]), partitions(p0), is_index_back=true, filter_before_indexback[true,true], range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(2,2,MAX ; 2,2,MIN)always false, --多了个always false range_cond([t1.c1 = 2]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.006 sec)
问题的风险及影响
稳定性问题。
影响版本
OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本。
解决方法
升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 Hotfix3 (oceanbase-3.2.3.3-107030052023031712) 及之后版本、V3.2.4 Hotfix2 (oceanbase-3.2.4.0-100020032023032115) 版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本。
规避方案
改写 SQL。
select * from t1 where ((c1 = 1 and c2 = 1) or (c1 = 2 and c2 = 2)) and c1 = 2 and c2 = 2;添加 FULL Hint 走主表。
select /*+full(t1)*/* from t1 where (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2;in 表达式后加 is true。
select * from t1 where (c1,c2) in ((1,1),(2,2)) is true and c1 = 2 and c2 = 2;
