IN 优化引入的正确性问题二

问题现象

存在 (c1,c2,c3,c4) 的索引，SQL 过滤条件中存在如下形式的过滤谓词 (c1 = ? or c1 = ?) and (c2 = ? or c2 = ?) and c3 = ? and c4 in (?,?)。其中 c1、c2 必须是 OR 的普通表达式，c3 是普通或者 IN 表达式，c4 必须是 IN 表达式。这个场景下会导致抽出的 range 缺失，引起执行结果出错。

举例： (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8);。

-- 计划的 range 中少了 [2,4,5,7];[2,4,5,8] 两个 range。
=============================================
|ID|OPERATOR  |NAME   |EST.ROWS|EST.TIME(us)|
---------------------------------------------
|0 |TABLE SCAN|t1(idx)|1       |5           |
=============================================
Outputs & filters:
-------------------------------------
  0 - output([t1.c1TC.], [t1.c2TC.], [t1.c3TC.], [t1.c4TC.], [t1.c5TC.]), filter(nil),
rowset=256
      access([t1.__pk_incrementTC.], [t1.c1TC.], [t1.c2TC.], [t1.c3TC.], [t1.c4TC.], [t1.c5TC.]),
partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([t1.c1TC.], [t1.c2TC.], [t1.c3TC.], [t1.c4TC.], [t1.__pk_incrementTC.]),
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.c3TC. = 5TC.], [t1.c1TC. = 1TC. OR t1.c1TC. = 2TC.TC.],
[t1.c2TC. = 3TC. OR t1.c2TC. = 4TC.TC.], [t1.c4TC. IN (7, 8)TC.TC.])

关键诊断信息

触发条件

存在 (c1,c2,c3,c4) 的索引，SQL 过滤条件中存在如下形式的过滤谓词 (c1 = ? or c1 = ?) and (c2 = ? or c2 = ?) and c3 = ? and c4 in (?,?)。其中 c1、c2 必须是 OR 的普通表达式，c3 是普通或者 IN 表达式，c4 必须是 IN 表达式。

事前巡检

检查是否存在上述形式的谓词和对应的索引。

事后诊断

explain 计划，检查生成的 range 中是否缺失了对应的 range。

问题原因

IN 优化引入的 BUG。

问题的风险及影响

SQL 执行结果出错。

影响租户

影响 OceanBase 数据库中的 SYS 租户和 Oracle 租户以及 MySQL 租户。

影响版本

OceanBase 数据库企业版本 V3.2.3 BP6（oceanbase-3.2.3.3-106000102022111521）及之后版本、V3.2.4 GA（oceanbase-3.2.4.0-100000072022102819）及之后版本、V4.1.0 GA（oceanbase-4.1.0.0-100001122023040322）及之后版本。

解决方法

• 解决方法一：

  升级至问题已修复版本。目前已修复的版本包括 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）及之后版本。

• 解决方法二：

  1. 将 IN 表达式改成等价的 OR 表达式，例如 c1 in ('1','2') => (c1 = '1' or c1 = '2')。

  2. 通过 Hint 禁止走包含 IN 表达式所在列的索引，例如 select /+full(t1)/...。

  3. 在 IN 表达式后面加个 is true（适用于命中主键索引的场景），例如 c1 in ('1','2') is true。

  4. 如果出现问题的 OceanBase 数据库版本为以下版本或更高，可以通过隐藏配置项 _enable_in_range_optimization 关闭 IN 优化。

     • OceanBase 数据库企业版 V3.2.3 BP8 Hotfix5（oceanbase-3.2.3.3-108050012023070409）及之后版本。

     • OceanBase 数据库企业版 V3.2.4 BP4（oceanbase-3.2.4.4-104000052023062021）及之后版本。

     • OceanBase 数据库企业版 V4.1.0 BP2（oceanbase-4.1.0.1-102000042023061309）及之后版本。

规避方式

无。

相关文档

关于 IN 优化引入的其他正确性问题，参见：IN 优化引入的正确性问题一、IN 优化引入的正确性问题三、IN 优化引入的正确性问题四。