10204升級到10205後同一sql報告ora-01719

OS： HP
ORACLE： 10205
上週將該資料庫從10204升級到10205，之後就有一條sql一直報告ora-01719
該sql結構如下
select a.xx
...
b.xx
from a, b
where a.class ='D'
and a.cd in ('A','E')
and ((a.cust_id = b.cust_id(+)) and 'D'='F') or
('D'='D') and (a.cust_id = b.cust_id(+)).

沒有升級前，該sql一直相安無事，升級後就一直執行不成功
登陸資料庫檢視，allow (+) in OR clause 明明已經存在了

SQL> select optimizer_feature_enable, description from v$session_fix_control where session_id = userenv('sid') and bugno = 6610822;

OPTIMIZER_FEATURE_ENABLE

-------------------------

DESCRIPTION

----------------------------------------------------------------

10.2.0.5

allow (+) in OR clause

且_eliminate_common_subexpr一直為true，從未改動過；

何以升級了反而出錯哪

我嘗試在會話級別將optimizer_feature_enable設定為10.2.0.4，然後執行sql，依舊報錯

表b的cust_id為not null

發了SR給ORACLE，收到回覆如下

In Oracle 10.2.0.4 and before, there is a bug in "common subexpression elimination" function. The bug number is 5346187. It can lead wrong transformation.
It was fixed on 10.2.0.5.

If setting the hidden parameter "_eliminate_common_subexpr" to false to disable the "common subexpression elimination", the ORA-1719 error will be report.
Please refer Bug 5346187, Note 5346187.8 and the last test case.


We recommend you to contact your developer to modify the SQL statement.
For example,

Using "unoin all" instead of "OR" operator.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production



SQL> alter session set "_eliminate_common_subexpr"=false;

Session altered.

SQL>
SQL> select d.deptno,e.empno,e.deptno from
dept d, emp e
where d.deptno in(10,20)
and d.deptno = e.deptno(+) and 'D'='D'
or (d.deptno = e.deptno(+) and 'D' = 'F' and d.deptno in(40)); 2 3 4 5
or (d.deptno = e.deptno(+) and 'D' = 'F' and d.deptno in(40))
*
ERROR at line 5:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN


SQL> alter session set "_eliminate_common_subexpr"=true;

Session altered.

SQL> select d.deptno,e.empno,e.deptno from
dept d, emp e
where d.deptno in(10,20)
and d.deptno = e.deptno(+) and 'D'='D'
or (d.deptno = e.deptno(+) and 'D' = 'F' and d.deptno in(40)); 2 3 4 5

DEPTNO EMPNO DEPTNO
---------- ---------- ----------
20 7369 20
20 7566 20
10 7782 10
20 7788 20
10 7839 10
20 7876 20
20 7902 20
10 7934 10

8 rows selected.

也就是說這種sql語法本身就是一個bug才會讓其編譯透過的，10205修復了這個bug；

報告ora-1719才是正常行為，且一旦該bug修復，無法透過_fix_control='7148689:off'
修復，唯一的方法是修改sql