问题现象
问题 SQL 语句中包含了 ORDER BY DESC 逆序排列的关键字。问题 SQL 最终的执行结果中，因为 OBServer 存储层的 BUG，未将已经 DELETE 掉的行过滤掉，导致 ORDER BY DESC 的返回结果（e.g. 返回行数）与去掉 ORDER BY DESC 的执行结果（e.g. 返回行数）不同且错误。
复现场景如下：
创建测试表 t0，t1。
obclient> CREATE TABLE t0(pk int primary key,c1 int,c2 int); Query OK, 0 rows affected (0.052 sec) obclient> CREATE TABLE t1(pk int primary key,c1 int,c2 int); Query OK, 0 rows affected (0.030 sec)插入测试数据。
obclient> INSERT INTO t1 VALUES(1,2,1),(2,2,1),(3,2,1),(4,2,0),(5,3,1),(6,3,1),(7,3,1),(8,4,0),(9,4,1),(10,4,1),(11,4,1),(12,4,1),(13,4,1),(14,4,1); Query OK, 14 rows affected (0.017 sec) Records: 14 Duplicates: 0 Warnings: 0 obclient> INSERT INTO t0 VALUES(1,2,3); Query OK, 1 row affected (0.006 sec)存储层合并。
obclient> ALTER SYSTEM MAJOR FREEZE; Query OK, 0 rows affected (0.008 sec)删除数据。
obclient> DELETE FROM t1 WHERE pk=2; -- DELETE 操作 Query OK, 1 row affected (0.003 sec)查询语句降序排序。
obclient> SELECT /*+ leading(t0,t1) use_nl(t0,t1) NO_USE_NL_MATERIALIZATION(t1)*/ t1.pk,t1.c2 FROM t1 join t0 on t0.c1=t1.c1 WHERE t1.c2=1 ORDER BY t1.pk DESC;返回结果如下:
+----+------+ | pk | c2 | +----+------+ | 3 | 1 | | 2 | 1 | -- 错误的返回结果 | 1 | 1 | +----+------+ 2 rows in set (0.012 sec)查询语句默认升序。
obclient> SELECT /*+ leading(t0,t1) use_nl(t0,t1) NO_USE_NL_MATERIALIZATION(t1)*/ t1.pk,t1.c2 FROM t1 join t0 on t0.c1=t1.c1 WHERE t1.c2=1 ORDER BY t1.pk;返回结果如下:
+----+------+ | pk | c2 | +----+------+ | 1 | 1 | | 3 | 1 | +----+------+ 2 rows in set (0.003 sec)用 EXPLAIN EXTENDED 命令展示详细执行计划信息。
将有问题的 SQL 进行 EXPLAIN EXTENDED 展示执行计划后，SQL 的执行有以下特征。
SQL 是是一个 Nested Loop Join 操作，执行计划详情部分可以看到 SQL 启动了
batch nested loop join优化（NESTED-LOOP JOIN + batch_join=true）。右表执行 table scan 执行逆序扫描（Reverse 关键字）。
EXPLAIN EXTENDED SELECT /*+ log_level(debug) leading(t0,t1) use_nl(t0,t1) NO_USE_NL_MATERIALIZATION(t1)*/ t1.pk,t1.c2 FROM t1 join t0 on t0.c1 = t1.c1 WHERE t1.c2 = 1 ORDER BY t1.pk DESC;返回结果如下:
+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |SORT | |3 |96 | | | |1 |└─NESTED-LOOP JOIN | |3 |45 | | | |2 | ├─TABLE FULL SCAN|t0 |1 |42 | | | |3 | └─TABLE FULL SCAN|t1(Reverse)|7 |43 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.pk(0x7f1965a43d50)], [t1.c2(0x7f1965a434f0)]), filter(nil), rowset=256 | | sort_keys([t1.pk(0x7f1965a43d50), DESC]) | | 1 - output([t1.pk(0x7f1965a43d50)], [t1.c2(0x7f1965a434f0)]), filter(nil), rowset=256 | | conds([t0.c1(0x7f1965a42040) = t1.c1(0x7f1965a42320)(0x7f1965a41940)]), nl_params_(nil), batch_join=true | | 2 - output([t0.c1(0x7f1965a42040)]), filter(nil), rowset=256 | | access([t0.c1(0x7f1965a42040)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t0.pk(0x7f1965a44f90)]), range(MIN ; MAX)always true | | 3 - output([t1.pk(0x7f1965a43d50)], [t1.c1(0x7f1965a42320)], [t1.c2(0x7f1965a434f0)]), filter([t1.c2(0x7f1965a434f0) = 1(0x7f1965a42df0)]), rowset=256 | | access([t1.pk(0x7f1965a43d50)], [t1.c1(0x7f1965a42320)], [t1.c2(0x7f1965a434f0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t1.pk(0x7f1965a43d50)]), range(MIN ; MAX)always true
如果携带有上文中执行计划特征（Batch Nested Loop Join + 右表 Reverse table scan），执行 OBServer 的版本未包含该问题的修复，且有正确性问题，那么大概率是遇到了本文描述的问题。
关键诊断信息
触发条件
问题 SQL 语句中包含了 ORDER BY DESC 逆序排列的关键字。
将有问题的 SQL 进行 EXPLAIN EXTENDED 展示执行计划后，SQL 的执行有以下特征。
SQL 是是一个 Nested Loop Join 操作，执行计划详情部分可以看到 SQL 启动了
batch nested loop join优化（NESTED-LOOP JOIN + batch_join=true）。右表执行 table scan 执行逆序扫描（Reverse 关键字）。
执行计划如下所示。
+----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ---------------------------------------------------------- | | |0 |SORT | |3 |96 | | | |1 |└─NESTED-LOOP JOIN | |3 |45 | | | |2 | ├─TABLE FULL SCAN|t0 |1 |42 | | | |3 | └─TABLE FULL SCAN|t1(Reverse)|7 |43 | | | ========================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.pk(0x7f1965a43d50)], [t1.c2(0x7f1965a434f0)]), filter(nil), rowset=256 | | sort_keys([t1.pk(0x7f1965a43d50), DESC]) | | 1 - output([t1.pk(0x7f1965a43d50)], [t1.c2(0x7f1965a434f0)]), filter(nil), rowset=256 | | conds([t0.c1(0x7f1965a42040) = t1.c1(0x7f1965a42320)(0x7f1965a41940)]), nl_params_(nil), batch_join=true | | 2 - output([t0.c1(0x7f1965a42040)]), filter(nil), rowset=256 | | access([t0.c1(0x7f1965a42040)]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t0.pk(0x7f1965a44f90)]), range(MIN ; MAX)always true | | 3 - output([t1.pk(0x7f1965a43d50)], [t1.c1(0x7f1965a42320)], [t1.c2(0x7f1965a434f0)]), filter([t1.c2(0x7f1965a434f0) = 1(0x7f1965a42df0)]), rowset=256 | | access([t1.pk(0x7f1965a43d50)], [t1.c1(0x7f1965a42320)], [t1.c2(0x7f1965a434f0)]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t1.pk(0x7f1965a43d50)]), range(MIN ; MAX)always true
如果携带有上文中执行计划特征（Batch Nested Loop Join + 右表 Reverse table scan），执行 OBServer 的版本未包含该问题的修复，且有正确性问题，那么大概率是遇到了本文描述的问题。
问题原因
OBServer 存储层的 BUG，未将已经 DELETE 掉的行过滤掉，导致 ORDER BY DESC 的返回结果（e.g. 返回行数）与去掉 ORDER BY DESC 的执行结果（e.g. 返回行数）不同且错误。
问题的风险及影响
在携有该问题的 OBServer 版本中执行有 Batch Nested Loop Join 执行路径且右表逆序索引的 SQL 会有正确性问题，执行结果不正确。
影响租户
影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。
影响的版本
OceanBase 数据库企业版 V2.2.77 GA（oceanbase-2.2.77-20210508211731）及之后版本、V3.1.2 GA（oceanbase-3.1.2-20210618150922）及之后版本、V3.2.3 GA（oceanbase-3.2.3.0-20220418212020）及之后版本、V3.2.2 GA（oceanbase-3.2.2-20211130225726）及之后版本。
解决方法
升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V2.2.77 BP13（oceanbase-2.2.77-113000052022082622）、V3.1.2 BP9 Hotfix2（oceanbase-3.1.2-109020072022082510）、V3.1.2 BP9 Hotfix3（oceanbase-3.1.2-109030012022090518）、V3.1.2 BP10（oceanbase-3.1.2-110000022022092714）、V3.2.3.2 BP5（oceanbase-3.2.3.2-105000062022090916）、V3.2.2 BP1（oceanbase-3.2.2-20211215001504）。
在确定是该问题场景的情况下可尝试调整系统变量来临时关掉 Batch Nested Loop （
SET GLOBAL _nlj_batching_enabled=true）绕过该问题，命令如下。-- MySQL 模式 obclient> set _NLJ_BATCHING_ENABLED = false;-- Oracle 模式 obclient> set "_NLJ_BATCHING_ENABLED"=false;注意
长期关闭
Batch Nested Loop有可能会有造成 Nested Loop Join 的执行比原本变慢。
