子查询改写错误导致 SQL 查询结果为空的问题

问题现象

存在问题的 OceanBase 数据库版本中，原始 SQL 是一个子查询语句，原始 SQL 计划算子只有一个 0 号算子 TABLE SCAN，WHERE 条件中谓词 IN 和 NOT 条件都没有扫描，查询结果为空，不符合预期。

最小化用例复现。

1. 创建表。

   obclient [SYS]> CREATE TABLE t11 (c1 NUMBER(10),c2 NUMBER(10));
   Query OK, 0 rows affected (0.030 sec)
   

   obclient [SYS]> CREATE TABLE t22 (x1 NUMBER(10) PRIMARY KEY);
   Query OK, 0 rows affected (0.058 sec)
   

2. 插入数据。

   obclient [SYS]> INSERT INTO t11 VALUES (1,2);
   Query OK, 1 row affected (0.011 sec)
   

   obclient [SYS]> INSERT INTO t22 VALUES (1);
   Query OK, 1 row affected (0.004 sec)
   

3. 执行未添加 Hint 提示 /*+ NO_REWRITE */ 的子查询语句。

   obclient [SYS]> SELECT x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c2 FROM t11 a);
   

   输出结果如下：

   Empty set (0.002 sec)
   

   子查询语句的执行计划：

   obclient [SYS]> explain SELECT  x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c2 FROM t11 a);
   

   输出结果如下:

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                                                                                                                                                                                        |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ===================================
   |ID|OPERATOR  |NAME|EST. ROWS|COST|
   -----------------------------------
   |0 |TABLE SCAN|T22 |1        |46  |
   ===================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([T22.X1]), filter(nil), startup_filter([0]),
      access([T22.X1]), partitions(p0)
   |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.002 sec)
   

4. 执行添加 Hint 提示 /*+ NO_REWRITE */ 的子查询语句。

   下面为添加 Hint 提示 /*+ NO_REWRITE */ 计划输出结果符合预期。

   

   obclient [SYS]> SELECT /*+ NO_REWRITE */ x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c2 FROM t11 a);
   

   输出结果如下：

   +----+
   | X1 |
   +----+
   |  1 |
   +----+
   1 row in set (0.003 sec)
   

   子查询语句的执行计划：

   obclient [SYS]> explain SELECT /*+ NO_REWRITE */ x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c2 FROM t11 a);
   

   输出结果如下：

   +------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                           |
   +------------------------------------------------------------------------------------------------------+
   | =================================================                                                    |
   | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                                    |
   | -------------------------------------------------                                                    |
   | |0 |SUBPLAN FILTER   |    |1       |12          |                                                    |
   | |1 |├─TABLE FULL SCAN|T22 |1       |4           |                                                    |
   | |2 |├─TABLE FULL SCAN|A   |1       |4           |                                                    |
   | |3 |└─TABLE FULL SCAN|A   |1       |4           |                                                    |
   | =================================================                                                    |
   | Outputs & filters:                                                                                   |
   | -------------------------------------                                                                |
   |   0 - output([T22.X1]), filter([T22.X1 = ANY(subquery(1))], [T22.X1 != ALL(subquery(2))]), rowset=16 |
   |       exec_params_(nil), onetime_exprs_(nil), init_plan_idxs_([1], [2]), use_batch=false             |
   |   1 - output([T22.X1]), filter(nil), rowset=16                                                       |
   |       access([T22.X1]), partitions(p0)                                                               |
   |       is_index_back=false, is_global_index=false,                                                    |
   |       range_key([T22.X1]), range(MIN ; MAX)always true                                               |
   |   2 - output([A.C1]), filter(nil), rowset=16                                                         |
   |       access([A.C1]), partitions(p0)                                                                 |
   |       is_index_back=false, is_global_index=false,                                                    |
   |       range_key([A.__pk_increment]), range(MIN ; MAX)always true                                     |
   |   3 - output([A.C2]), filter(nil), rowset=16                                                         |
   |       access([A.C2]), partitions(p0)                                                                 |
   |       is_index_back=false, is_global_index=false,                                                    |
   |       range_key([A.__pk_increment]), range(MIN ; MAX)always true                                     |
   +------------------------------------------------------------------------------------------------------+
   

关键诊断信息

事前巡检

命中这个 BUG 的 SQL 是一个包含两个子查询的 SQL，子查询会出现在 WHERE 条件中谓词 IN 和 NOT IN 的后面，并且子查询查询的表是同一张表的不同列（子查询查询是同一张表 t11 的不同列 c1 和 c2 列）下面是示例 SQL。

SELECT  x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c2 FROM t11 a);

事后诊断

存在问题的 SQL 包含两个子查询，子查询中查询的表是同一张表的不同列，并且计划中只有一个 0 号算子 TABLE SCAN，执行结果为无数据不符合预期，通过加 Hint /*+ NO_REWRITE */ 可以得到预期正确结果，可以判断为此问题。

问题原因

子查询合并时，没有校验两个 subquery 的投影项是否一致，SQL 的两个 subquery 的投影项是不同的，但是却做了子查询合并改写，误认为投影项是相同的，当做恒假的语句做了子查询合并改写导致结果为空。

SELECT  x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c2 FROM t11 a);

说明谓词 IN 和 NOT IN 后面是子查询，子查询表 t11 是相同表的不同列 c1 和 c2 列，如果列名相同则可以合并如下面 SQL。

SELECT  x1 FROM t22 WHERE x1 IN (SELECT a.c1 FROM t11 a) AND x1 NOT IN (SELECT a.c1 FROM t11 a);

问题的风险及影响

存在问题的 OceanBase 数据库执行 SQL 如果命中子查询合并 BUG 会导致正确性问题。

影响租户

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

影响的版本

OceanBase 数据库企业版 V2.2.77 BP18（oceanbase-2.2.77-118000012023052520）之后版本、V3.1.2 BP11 (oceanbase-3.1.2-111000052023010412) 及之后版本、V3.2.3 BP10 Hotfix3 (oceanbase-3.2.3.3-110030012023101913) 及之后版本、V4.1.0 BP2 (oceanbase-4.1.0.1-102000042023061309) 及之后版本。

解决方法

升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V2.2.77 BP19（oceanbase-2.2.77-119000122024060513）、V3.2.3 BP10 Hotfix4（oceanbase-3.2.3.3-110040022023110614）、V3.2.3 BP10 Hotfix5（oceanbase-3.2.3.3-110050012023112210）、V3.2.3 BP10 Hotfix6（oceanbase-3.2.3.3-110060012024011615）、V3.2.3 BP10 Hotfix7（oceanbase-3.2.3.3-110070012024012316）、V3.2.3 BP10 Hotfix8（oceanbase-3.2.3.3-110080012024022713）、V3.2.3 BP10 Hotfix9（oceanbase-3.2.3.3-110090012024041611）、V3.2.3 BP10 Hotfix10（oceanbase-3.2.3.3-110100012024042814）、V3.2.3 BP10 Hotfix11（oceanbase-3.2.3.3-110110032024052217）、V3.2.3 BP10 Hotfix12（oceanbase-3.2.3.3-110120012024060510）、V3.2.3 BP10 Hotfix13（oceanbase-3.2.3.3-110130012024061819）、V3.2.3 BP10 Hotfix14（oceanbase-3.2.3.3-110140012024082010）、V3.2.3 BP10 Hotfix15（oceanbase-3.2.3.3-110150012024102111）、V3.2.3 BP10 Hotfix16（oceanbase-3.2.3.3-110160012025021315）、V3.2.3 BP10 Hotfix17（oceanbase-3.2.3.3-110170012025032112）、V3.2.3 BP10 Hotfix18（oceanbase-3.2.3.3-110180012025060316）、V3.2.3 BP10 Hotfix19（oceanbase-3.2.3.3-110190022025082615）、V3.2.3 BP10 Hotfix20（oceanbase-3.2.3.3-110200012025111317）、V3.2.3 BP11（oceanbase-3.2.3.3-111000032024070822）、V4.1.0 BP3（oceanbase-4.1.0.2-103000072023081111）。

规避方式

使用 Hint 提示 /*+ NO_REWRITE */ 禁止改写。