/*
Bug: Correlated subquery using "max()" and "where rownum <=1" returns null/empty values
- tested to work correctly in Oracle 11.2.0.1.0
- tested to fail in Oracle 12.1.0.2.0
- occurs only for certain values
-> the testdata below contains the keys 5 and 24036 in both tables.
-> The subquery returns the correct string for the key 5
-> The subquery returns NULL for the key 24036 (Bug! Should also return 'noreply@dummy.com')
- the bug only occurs with both the max() aggregate AND the "rownum <= 1" criteria.
*/
-- Setup:
drop table t1;
create table t1 (
id number(8),
mailadress varchar2(512)
);
insert into t1 values (5, 'noreply@dummy.com');
insert into t1 values (24036, 'noreply@dummy.com');
drop table t2;
create table t2 (
id number
);
insert into t2 values (5);
insert into t2 values (24036);
-- Test query:
select (
select max(mailadress)
from t1
where t1.id = t2.id
and rownum <= 1
) as mailaadress
from
t2
where t2.id = 24036;
Can anyone confirm this behavior on Oracle 12c or am I alone?
Regards,
Oliver