rownum 条件含子查询时 SQL 结果不正确

问题现象

Oracle 租户模式下，SQL 中 rownum 条件含针对实体表的子查询条件时，查询结果不正确。

示例如下：

1. 创建表。

   obclient > create table a11(id int, codetype varchar2(50), code varchar2(50));
   Query OK, 0 rows affected (0.128 sec)
   

   obclient > create table b11(id int, codetype varchar2(50), code varchar2(50));
   Query OK, 0 rows affected (0.031 sec)
   

2. 插入数据。

   obclient > insert into b11 values (3, 'asdasd', 'sdasdas');
   Query OK, 1 row affected (0.022 sec)
   

   obclient > insert into a11 values (2, 'sdasdasd', 'sdsadas');
   Query OK, 1 row affected (0.003 sec)
   

   obclient > insert into a11 values (3, 'fdasdsa', 'asassda');
   Query OK, 1 row affected (0.000 sec)
   

3. 查询结果正确的 SQL（子查询条件中查询的表为 dual 表）。

   obclient > select * from a11 where rownum <= (select 3 from dual);
   

   输出结果如下:

    +------+----------+---------+
    | ID   | CODETYPE | CODE    |
    +------+----------+---------+
    |    2 | sdasdasd | sdsadas |
    |    3 | fdasdsa  | asassda |
    +------+----------+---------+
    2 rows in set (0.048 sec)
   

4. 查询结果错误的 SQL1（子查询条件中查询的表为实体表）。

   obclient > select * from a11 where rownum <= (select 3 from b11);
   Empty set (0.001 sec)
   

5. 查询结果错误的 SQL2（子查询条件中查询的表为实体表）。

   obclient > select * from a11 where rownum <= (select id from b11);
   Empty set (0.004 sec)
   

6. 查询结果错误的 SQL2 的执行计划。

   obclient > explain select * from a11 where rownum <= (select id from b11);
   

   输出结果如下:

    | ==================================================
    |ID|OPERATOR             |NAME    |EST. ROWS|COST|
    --------------------------------------------------
    |0 |COUNT                |        |2        |92  |
    |1 | SUBPLAN FILTER      |        |2        |92  |
    |2 |  TABLE SCAN         |A11     |2        |46  |
    |3 |  PX COORDINATOR     |        |1        |46  |
    |4 |   EXCHANGE OUT DISTR|:EX10000|1        |46  |
    |5 |    TABLE SCAN       |B11     |1        |46  |
    ==================================================
   
    Outputs & filters:
    -------------------------------------
    0 - output([A11.ID(0x7ef3050b3a40)], [A11.CODETYPE(0x7ef3050b3d30)], [A11.CODE(0x7ef3050b4020)]), filter(nil), rownum_limit_expr_(cast(FLOOR(?)(0x7ef30511fdf0), BIGINT(-1, 0))(0x7ef305120640))
    1 - output([A11.ID(0x7ef3050b3a40)], [A11.CODETYPE(0x7ef3050b3d30)], [A11.CODE(0x7ef3050b4020)]), filter(nil),
        exec_params_(nil), onetime_exprs_([subquery(1)(0x7ef30506a250)]), init_plan_idxs_(nil)
    2 - output([A11.ID(0x7ef3050b3a40)], [A11.CODETYPE(0x7ef3050b3d30)], [A11.CODE(0x7ef3050b4020)]), filter(nil),
        access([A11.ID(0x7ef3050b3a40)], [A11.CODETYPE(0x7ef3050b3d30)], [A11.CODE(0x7ef3050b4020)]), partitions(p0),
        is_index_back=false,
        range_key([A11.__pk_increment(0x7ef3050f2cf0)]), range(MIN ; MAX)always true
    3 - output([B11.ID(0x7ef3050b1640)]), filter(nil)
    4 - output([B11.ID(0x7ef3050b1640)]), filter(nil), is_single, dop=1
    5 - output([B11.ID(0x7ef3050b1640)]), filter(nil),
        access([B11.ID(0x7ef3050b1640)]), partitions(p0),
        is_index_back=false,
        range_key([B11.__pk_increment(0x7ef305118df0)]), range(MIN ; MAX)always true
    1 row in set (0.041 sec)
   

关键诊断信息

触发条件

Oracle 租户下，当 rownum 条件中包含针对实体表的子查询条件。

问题原因

在 Oracle 租户下，当 rownum 条件中包含针对实体表的子查询条件时，执行计划会生成 COUNT + SUBPLAN FILTER 算子（如示例中的 0 号和 1 号算子）。在问题版本（使用了新引擎的 V3.2.x 版本）中，COUNT 算子的 rownum_limit_expr_ （与 select id from b11 的子查询条件相对应） 是在 open 阶段就计算的，其计算逻辑中包含了一个 onetime expr，然而 onetime expr 在 SUBPLAN FILTER 算子中是在 get next row 阶段才会计算的，这就导致 COUNT 算子计算 rownum_list_expr 时，相应的 onetime expr 直接使用了默认值，从而导致查询结果不正确。

问题的风险及影响

SQL 查询结果不正确。

影响租户

影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。

影响的版本

OceanBase 数据库企业版 V3.2.3 GA（oceanbase-3.2.3.0-20220418212020）及之后版本、V3.2.4 GA（oceanbase-3.2.4.0-100000072022102819）及之后版本。

解决方法

• 升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.3 BP10（oceanbase-3.2.3.3-110000092023091219）及之后版本、V3.2.4 BP6（oceanbase-3.2.4.6-106000062023110109）及之后版本。

• 修改 SQL，rownum 条件中不要包含非相关子查询条件。

规避方式

rownum 条件使用常量，不要使用非相关子查询。