Optimizer Issue in Oracle 12.0.1.2: “Reduce Group By”

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 12.1.0.2.

Symptom:

An outer join query with a bind variable and a group by clause can produce wrong results in some cases.

Analysis:

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

Example:

 create table test1(c1 number(5),c2 varchar2(16));
 insert into test1 values(1,'3');
 commit;

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

Workaround:

alter session set "_optimizer_reduce_groupby_key" = false;

Please don’t use the workaround:
alter session set optimizer_features_enable=’12.1.0.1′;
as this will switch off other good 12.1.0.2 optimizer features working very well.

More information:

See MOS Note:20634449.8 describing:
Bug 20634449 – Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2

As far as I can see there are no interim (one-off/single) patches available right now.

–Mike