Migrating to 11.2.0.3 ORA-00976

You can have exists even in join condition in addition to what Rob Van Wijk has writen. We have code lines that has such exists together with an connect by query. Patching an 11.2.0.2 database to 11.2.0.3 In 11.2.0.2 one can run such a query. Here is one stupid example of such.

select d2.dummy from dual d1 left outer join dual d2 on exists (select 1 from dual connect by level < 3); 

D
-
X

In 11.2.0.3 you get an error thrown to your face.

ORA-00976: Specified pseudocolumn or operator not allowed here.
Cause: LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or CONNECT_BY_ISCYCLE was specified at an illegal location.
Action: Remove LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or CONNECT_BY_ISCYCLE.

The query may be rewritten like 11.2.0.3 parser is satisfied.

with cte as (select 1 from dual connect by level < 3)
select d2.dummy from dual d1 left outer join dual d2 on exists (select 1 from cte);

D
-
X

This is a runtime problem. Run your tests before patching production. Actually this is not just a runtime problem. We had the code inside a pl/sql procedure. The procedure is valid in 11.2.0.2 environment. After patching the procedure is still valid just waiting for the next compile or runtime problem of this code. Well there is most certainly a reason for such problem appearing. Might this be a side affect for a Bug correction 8724314 mentioned in 11.2.0.3 Patch Set - List of Bug Fixes by Problem Type [ID 1348303.1]. Might this usage of common table expression workaround be just a next bug waiting to appear.

Update: Confirmation about 8724314 being the reason for this
Bug 13496250 - Unexpected ORA-976 using LEVEL / PRIOR / ROWNUM in an ANSI "ON" filter predicate [ID 13496250.8]. The mentioned workaround there will not be a valid workaround always. If you start rewriting the query change to the common table expression approach described in this post.