Query featuring outer joins behaves differently in Oracle 12c

UPDATE: This is fixed in 12.1.0.2.

---

This definitely looks like a bug in 12.1.0.1. I would encourage you to create a service request through Oracle support. They might be able to find a fix or a better work around. And hopefully Oracle can fix it in a future version for everyone. Normally the worst part about working with support is reproducing the issue. But since you already have a very good test case this issue may be easy to resolve.

There are probably many ways to work around this bug. But it's difficult to tell which method will always work. Your query re-write may work now, but if optimizer statistics change perhaps the plan will change back in the future.

Another option that works for me on 12.1.0.1.0 is:

ALTER SESSION SET optimizer_features_enable='11.2.0.3';

But you'd need to remember to always change this setting before the query is run, and then change it back to '12.1.0.1' after. There are ways to embed that within a query hint, such as /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */. But for some reason that does not work here. Or perhaps you can temporarily set that for the entire system and change it back after a fix or better work around is available.

Whichever solution you use, remember to document it. If a query looks odd the next developer may try to "fix" it and hit the same problem.

Refer to:

ANSI Outer Join Query Returns Wrong Results After Upgrade to 12.1.0.1 (Doc ID 1957943.1)

Unpublished bug 16726638

Fixed in 12.1.0.2 (I have tested this)

Workaround (I tested in 12.1.0.1):

alter session set "_optimizer_ansi_rearchitecture"=false;          

Note 1957943.1 recommends as an alternative:

optimizer_features_enable = '11.2.0.4';

but that does not work.