10204升级到10205后报错ora-01719

myownstars

aaqwsh 发表于 2012-1-12 13:29
"_fix_control='7148689ff' "  seems useless.
It's my test:

不要使用dba_*视图 创建两个普通表再试一下  

Yong Huang

Now I carefully read your SQL. (Sorry I didn't do that earlier.) I tried to run your SQL in message #10 (the one about dba_users and dba_jobs) in Oracle 10.2.0.3, 10.2.0.4, 11.2.0.1, 11.2.0.3. It failed in all versions.

Oracle simply doesn't allow you to have the construct "A(+) or B" in the where-clause. The description for ORA-1719 says it clearly.

Are you sure your original query

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(+)).

works in 10.2.0.4? Can you create a small test case for us to reproduce?

Ignore the _fix_control parameter for now.

Yong Huang

myownstars

here is the test case
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

myownstars

SQL> alter session set events='10053 trace name context forever, level 1';

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' = 'D' 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
40


10053 Trace:

--After transformation:

SQL:******* UNPARSED QUERY IS *******
SELECT "D"."DEPTNO" "DEPTNO","E"."EMPNO" "EMPNO","E"."DEPTNO" "DEPTNO" FROM "SCOTT"."DEPT" "D","SCOTT"."EMP" "E"
WHERE 'D'='D' AND "D"."DEPTNO"="E"."DEPTNO"(+)
AND (("D"."DEPTNO"=10 OR "D"."DEPTNO"=20) OR "D"."DEPTNO"=40)

Current SQL statement for this session:
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(+) <<======This condition was merged.
and 'D' = 'D' and d.deptno in(40))

Yong Huang

Thanks for this test. It looks like after version 10.2.0.4, the parameter _eliminate_common_subexpr no longer has any effect. Its TRUE value does not eliminate common subexpressions.

Yong Huang