Possible bug in Oracle 11.2.0.2
We encountered today a possible Oracle bug which only occurs in a very specific situation: a subquery returning one row used further in a subcorrelated subquery. This is the statement (expected b = 1, returned b null):
SQL> with source as
2 (select a, b from (select 'a' a, 1 b from dual)),
3 result as
4 (select x.*
5 from source x
6 where not exists (select y.b
7 from source y
8 where 1 = 2
9 and x.b = y.b))
10 select * from result;
A B
- ———-
a
The same query works as expected if we change the first input to (select ‘a’ a, 1 b from dual union all select ‘b’ a, 2 b from dual) and also there are other workarounds:
- lose the subquery (with source as (select ‘a’ a, 1 b from dual));
- use nvl on the join condition (nvl(x.b,0) = nvl(y.b,0));
- add (and x.b is not null) in the subcorrelated subquery.
However, I offer a beer for an explication to the initial result (other than declaring it an Oracle bug).
P.S. 10.2.0.4 and 10.2.0.5 both return the expected result, the bug reproduces only on 11.2.0.2.