一、前言
最近在进行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上是查询不到任何数据。
当前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的强大。
四、问题处理
和我司研发测试人员沟通，他们反对对该SQL进行改写，认为Oracle能支持这类SQL，为什么OB就不能支持呢，如果要修改代码，所牵涉的工作量很大，因为还不清楚有多少代码还有这种情况，存在即真理。
于是和OB研发进行了沟通，他们认同了我司的研发测试的观点，但现阶段O不支持此类SQL，计划提交fix，进行排期，待后面的补丁对其进行处理。
我和OB的人调侃到，这么多年，我俩也是第一次见到这问题，涨知识了。
