一、前言

最近在进行Oracle到OB的兼容性测试，使用的是OB 4.2 企业版，相对于OB社区版，OB企业版对Oracle的兼容性还是比较不错的，并且提供官方技术支持。
目前在测的这套Oracle库业务比较复杂，已存在多年，去年升级到19C，体量也比较大，目前计划对其进行迁移验证测试。
业务测试中，OB对Oracle的兼容性还是要做一些适配改造。

二、问题描述

今天在测试的时候发现一个比较有意思的情况，让我接下来细细道来。
业务人员反馈在进行功能测试时异常，业务无法查询到数据，该功能在Oracle上查询数据正常，希望分析原因。
业务人员提供该功能的查询SQL语句，经脱敏后语句内容如下：

select t.exp_id, t.prod_id,p.com_id
  from opik.expo_prod_mai t,
       opik.cor_pro_en_mai_chk p,
       (select *
          from opik.expo_samp_info
         where del_flag = '0'
           and exp_id = 102824) s
 where t.prod_id = p.prod_id
   and t.com_id = p.com_id
   and t.REC_ID = s.EXP_PRO_MAI_ID(+)
   and t.REC_ID = s.EXP_PRO_MAI_ID(+)
   and t.status != '4'
   and t.status != '5'
   and t.exp_id = 102824;
复制

这是一条带有左连接的SQL查询，这条SQL语句在Oracle库查询正常，可以正常返回条数，但在OB上查询不到任何数据。而且初期使用4.1.2版本的ODC查询时是无法查看执行计划的，4.1.2版本的ODC查看执行计划报错如下。

无法查询到数据。
更换了新版的4.2.2 版本ODC可以查看到SQL的执行计划，但也是查询不到数据。
将这条SQL分别在Oracle服务器和OB服务器上执行，SQL语句都能执行，但得到的结果不同，Oracle是77条，OB是没有任何数据返回。
OB库的数据是从Oracle测试库使用OMS全量同步过来的，两边的数据比对也都是相同的。

三、问题排查

接下来分别在Oracle和OB数据库分别进行查询对比下。
Oracle查询：

SQL> alter session set statistics_level=all;

Session altered.

Elapsed: 00:00:00.00
SQL> select t.expo_id, t.prod_id,p.com_id
  2    from opik.expo_prod_mai t,
  3         opik.cor_pro_en_mai_chk p,
  4         (select *
  5            from opik.expo_samp_info
  6           where del_flag = '0'
  7             and expo_id = 102824) s
  8   where t.prod_id = p.prod_id
  9     and t.com_id = p.com_id
 10     and t.REC_ID = s.EXP_PRO_MAI_ID(+)
 11     and t.REC_ID = s.EXP_PRO_MAI_ID(+)
 12     and t.status != '4'
 13     and t.status != '5'
 14     and t.expo_id = 102824;
    102824 1902575064  614998074
    --- 此处省略部分结果数据
    102824  728828045  614998074
    102824 1902574044  614998074

77 rows selected.

Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
SQL_ID  01gqtadhtz1p6, child number 1
-------------------------------------
select t.expo_id, t.prod_id,p.com_id   from
opik.expo_prod_mai t,
opik.cor_pro_en_mai_chk p,        (select *           from
opik.expo_samp_info          where del_flag = '0'
 and expo_id = 102824) s  where t.prod_id = p.prod_id    and t.com_id =
p.com_id    and t.REC_ID = s.EXP_PRO_MAI_ID(+)    and t.REC_ID =
s.EXP_PRO_MAI_ID(+)    and t.status != '4'    and t.status !=
'5'    and t.expo_id = 102824

Plan hash value: 700051605

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                               |      1 |        |     77 |00:00:00.01 |     179 |
|   1 |  NESTED LOOPS                         |                               |      1 |     52 |     77 |00:00:00.01 |     179 |
|   2 |   NESTED LOOPS OUTER                  |                               |      1 |     52 |     82 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| expo_prod_mai                 |      1 |     52 |     82 |00:00:00.01 |      15 |
|*  4 |     INDEX RANGE SCAN                  | IDX_expo_prod_mai_3           |      1 |     52 |     82 |00:00:00.01 |       2 |
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| expo_samp_info                |     82 |      1 |      0 |00:00:00.01 |       3 |
|*  6 |     INDEX RANGE SCAN                  | IDX_expo_samp_info_4          |     82 |      1 |      0 |00:00:00.01 |       3 |
|*  7 |   INDEX RANGE SCAN                    | I_cor_pro_en_mai_chk_0        |     82 |      1 |     77 |00:00:00.01 |     161 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."EXPO_ID"=102824)
       filter(("T"."STATUS"<>'4' AND "T"."STATUS"<>'5'))
   5 - filter(("EXPO_ID"=102824 AND "del_flag"=0))
   6 - access("T"."REC_ID"="expo_samp_info"."EXP_PRO_MAI_ID")
       filter("T"."REC_ID"="expo_samp_info"."EXP_PRO_MAI_ID")
   7 - access("T"."COM_ID"="P"."COM_ID" AND "T"."PROD_ID"="P"."PROD_ID")

Note
-----
   - this is an adaptive plan


40 rows selected.

Elapsed: 00:00:00.43
SQL> select count(*) from opik.expo_prod_mai;
     18274

Elapsed: 00:00:00.05
SQL> select count(*) from opik.cor_pro_en_mai_chk;
  10172282

Elapsed: 00:00:02.12
SQL> select count(*) from opik.expo_samp_info;
        43

Elapsed: 00:00:00.01

--- SQL语句里的自关联查询返回0条数据也是正常的
SQL> select count(*) 
  2            from opik.expo_samp_info
  3           where del_flag = '0'
  4             and expo_id = 102824;
         0

Elapsed: 00:00:00.00

复制

可以看到这条SQL语句在Oracle上查询正常，可以正常返回77条数据。

接着，再看下OB的查询，以下是在OB数据库服务器上的查询。
OB查询
代码如下

obclient [oapki]> select t.expo_id, t.prod_id,p.com_id
    ->   from opik.expo_prod_mai  t,
    ->        opik.cor_pro_en_mai_chk  p,
    ->        (select *
    ->           from opik.expo_samp_info
    ->          where delete_flag = '0'
    ->            and expo_id = 102824) s
    ->  where t.prod_id = p.prod_id
    ->    and t.com_id = p.com_id
    ->    and t.REC_ID = s.EXPO_PRODUCT_MAINT_ID(+)
    ->    and t.REC_ID = s.EXPO_PRODUCT_MAINT_ID(+)
    ->    and t.status != '4'
    ->    and t.status != '5'
    ->    and t.expo_id = 102824;
Empty set (0.089 sec)

obclient [oapki]> select count(*) from opik.expo_prod_mai ;
+----------+
| COUNT(*) |
+----------+
|    18274 |
+----------+
1 row in set (0.042 sec)

obclient [oapki]> select count(*) from opik.cor_pro_en_mai_chk ;
+----------+
| COUNT(*) |
+----------+
| 10172282 |
+----------+
1 row in set (0.101 sec)

obclient [oapki]> select count(*) from opik.expo_samp_info;
+----------+
| COUNT(*) |
+----------+
|       43 |
+----------+
1 row in set (0.068 sec)

obclient [oapki]> select count(*) 
    ->           from opik.expo_samp_info
    ->          where delete_flag = '0'
    ->            and expo_id = 102824;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.047 sec)
复制

可以看到相同的SQL在OB上是查询不到任何数据。

ODC 4.2.2 查看到的执行计划如下：

当前OB数据库的数据是通过OB的OMS全量同步过来的，包括函数、存储过程、触发器、索引、JOB等都是全量同步过来的，并对两边的对象做过对比是一致的。
这条SQL里的子查询条件是可以正常执行，且返回结果相同，但完整的SQL却无法在OB上获取争取结果。

问题到底出在哪呢，再回过头好好审查下这段SQL语句，才发现这条语句非常怪异，什么会出现两个相同的查询条件，如下所示：
and t.REC_ID = s.EXP_PRO_MAI_ID(+)
and t.REC_ID = s.EXP_PRO_MAI_ID(+)
这么写有什么目的，是写错了，还是故意就这么写。
于是和测试研发人员进行了沟通，得到的答复是他们也不知道为什么会这么写，而且数据库里也不止一处有这样的情况。
我们也不去追究造成这一问题的历史原因，因为这个库的历史比研发的工作年限都长，甚至可以说都超多很多研发的年龄了，已无法追究历史原因。
但神奇的是这么风骚的代码在Oracle下它是可以正常执行而且能正常返回结果的，但OB认为这段SQL代码不符合代码规范，是不支持的。
如果对代码进行改写，去掉代码重复的左连接关联条件，只保持一条and t.REC_ID = s.EXP_PRO_MAI_ID(+)，再在Oracle和OB上执行得到的结果都是相同的。

你甚至不得不佩服Oracle的强大，即使这种很不规范的代码，也能在Oracle上正常执行。

四、问题处理

和我司研发测试人员沟通，他们反对对该SQL进行改写，认为Oracle既然能支持这类SQL，为什么OB就不能支持呢，如果要修改代码，所牵涉的工作量巨大，因为还不清楚有多少代码还有这种情况，后期会牵涉到大量的业务代码改造，工期就会更长。

如果站在研发的角度去考虑，确实是这样，因为存在即真理。

于是和OB研发进行了沟通，他们也认同了我司研发测试的观点，但现阶段OB不支持此类SQL，计划提交fix，进行排期，待后面的补丁对其进行处理。

我和OB的人调侃到，这么多年，我俩也是第一次见到这问题，涨知识了。 ^_

不知看了这篇文章的你是否也有所收获，评论区也谈谈你的感想和遇到过的奇葩代码。