DBAs biggest fears I'd guess are Optimizer Wrong Query Results bugs as usually the optimizer does not write a message into the alert.log saying "Sorry, I was in a bad mood today ...".
The Oracle Database Optimizer is a complex piece - and in Oracle 12c it delivers great performance results. Plus (my personal experience when you know what to do) it is more predictable which I like a lot when changing databases from one to another release. But due to its complexity sometimes we see issues - and sometimes it is necessary to switch off tiny little pieces until a fix is available.
Roy just came across this one - and we believe it's worth to tell you about it. Again, our intention is only to prevent issues when upgrading or migrating to Oracle Database 22.214.171.124.
An outer join query with a bind variable and a group by clause can produce wrong results in some cases.
If all of the following match, you may be hitting this bug:
- two or more subquery views are outer-joined on column C1
- column C1 is specified on select list of top-most query block
- column C1 is filtered on a bind value
create table test1(c1 number(5),c2 varchar2(16));
insert into test1 values(1,'3');
set NULL NULL
variable num1 number
execute :num1 :=1;
-- Following query retuns wrong result(NULL), this should return 1.
select V.c1 from
(SELECT c1 FROM test1 GROUP BY c1) V,
(SELECT c1 FROM test1 WHERE c2 = '1' GROUP BY c1) V2
where V.c1 = :num1
and V.c1 = V2.c1(+);
alter session set "_optimizer_reduce_groupby_key" = false;
Please don't use the workaround:
alter session set optimizer_features_enable='126.96.36.199';
as this will switch off other good 188.8.131.52 optimizer features working very well.
See MOS Note:20634449.8 describing:
Bug 20634449 - Wrong results from
OUTER JOIN with a bind variable and a GROUP BY clause in 184.108.40.206
As far as I can see there are no interim (one-off/single) patches available right now.