SU bypassed: More than 1 column in connect condition

A recent thread on oracle-l discussed a CBO regression that was encountered following 19.8->19.9 Release Update application.
A simplified example that demonstrates the observed behavior:

create table departments
as
select rownum department_id,
       10 manager_count,
       100 employee_count
  from dual
  connect by level<=10;

create table employees
as
select rownum employee_id,
       department_id,
       lpad('x', 100, 'x') name
  from departments,
       lateral(
         select level 
           from dual
           connect by level<=employee_count
       )(+);

alter session set events 'trace[sql_optimizer.*]';
explain plan for
select *
  from departments d
 where manager_count > employee_count
                       + (select --+ unnest
                                 count(*)
                            from employees e
                           where e.department_id = d.department_id);

The last query has the following execution plans in 19.8 and 19.9 correspondingly:

-- ----------------------------------------------------------------------------------
-- 19.8
-- ----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1545815388

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    35 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN           |             |     1 |    35 |    11  (10)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1     |    10 |   260 |     8  (13)| 00:00:01 |
|   3 |    HASH GROUP BY     |             |    10 |    30 |     8  (13)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMPLOYEES   |  1000 |  3000 |     7   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | DEPARTMENTS |    10 |    90 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_0"="D"."DEPARTMENT_ID")
       filter("MANAGER_COUNT">"EMPLOYEE_COUNT"+"COUNT(*)")

18 rows selected.

-- ----------------------------------------------------------------------------------
-- 19.9
-- ----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1015098386

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |     1 |     9 |    38   (0)| 00:00:01 |
|*  1 |  FILTER             |             |       |       |            |          |
|   2 |   TABLE ACCESS FULL | DEPARTMENTS |    10 |    90 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |             |     1 |     3 |            |          |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |   100 |   300 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MANAGER_COUNT">"EMPLOYEE_COUNT"+ (SELECT /*+ UNNEST */
              COUNT(*) FROM "EMPLOYEES" "E" WHERE "E"."DEPARTMENT_ID"=:B1))
   4 - filter("E"."DEPARTMENT_ID"=:B1)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   3 -  SEL$2
         U -  unnest / Failed basic validity checks

25 rows selected.

It can be seen that subquery unnesting does not happen in 19.9 anymore. Even worse than that, the outline/SQL Plan Baseline from 19.8 fails to reproduce in 19.9. That change by itself can have an adverse effect on the existing queries, e.g.:

SQL> select --+ gather_plan_statistics
  2         *
  3    from departments d
  4   where manager_count > employee_count
  5                         + (select --+ unnest
  6                                   count(*)
  7                              from employees e
  8                             where e.department_id = d.department_id);

no rows selected

SQL> select * from dbms_xplan.display_cursor(format=> 'allstats last');

-- ----------------------------------------------------------------------------------
-- 19.8
-- ----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8gcr6b9xkkqv0, child number 0
-------------------------------------

Plan hash value: 1545815388

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |      1 |        |      0 |00:00:00.01 |      20 |     16 |       |       |          |
|*  1 |  HASH JOIN           |             |      1 |      1 |      0 |00:00:00.01 |      20 |     16 |  2078K|  2078K|  551K (0)|
|   2 |   VIEW               | VW_SQ_1     |      1 |     10 |     10 |00:00:00.01 |      18 |     16 |       |       |          |
|   3 |    HASH GROUP BY     |             |      1 |     10 |     10 |00:00:00.01 |      18 |     16 |  1558K|  1558K|  666K (0)|
|   4 |     TABLE ACCESS FULL| EMPLOYEES   |      1 |   1000 |   1000 |00:00:00.01 |      18 |     16 |       |       |          |
|   5 |   TABLE ACCESS FULL  | DEPARTMENTS |      1 |     10 |     10 |00:00:00.01 |       2 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_0"="D"."DEPARTMENT_ID")
       filter("MANAGER_COUNT">"EMPLOYEE_COUNT"+"COUNT(*)")


27 rows selected.

-- ----------------------------------------------------------------------------------
-- 19.9
-- ----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8gcr6b9xkkqv0, child number 0
-------------------------------------

Plan hash value: 1015098386

------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |      0 |00:00:00.01 |     182 |     16 |
|*  1 |  FILTER             |             |      1 |        |      0 |00:00:00.01 |     182 |     16 |
|   2 |   TABLE ACCESS FULL | DEPARTMENTS |      1 |     10 |     10 |00:00:00.01 |       2 |      0 |
|   3 |   SORT AGGREGATE    |             |     10 |      1 |     10 |00:00:00.01 |     180 |     16 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |     10 |    100 |   1000 |00:00:00.01 |     180 |     16 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MANAGER_COUNT">"EMPLOYEE_COUNT"+)
   4 - filter("E"."DEPARTMENT_ID"=:B1)


26 rows selected.

The buffer gets goes from 20 in 19.8 to 182 in 19.9 for this simple query.
The 10053 trace file provides more details regarding that SU bypass:

SQE: Query after SQ elimination:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID",
       "D"."MANAGER_COUNT" "MANAGER_COUNT",
       "D"."EMPLOYEE_COUNT" "EMPLOYEE_COUNT"
  FROM "TC"."DEPARTMENTS" "D"
 WHERE "D"."MANAGER_COUNT">"D"."EMPLOYEE_COUNT"
                          + (SELECT /*+ UNNEST */ 
                                    COUNT(*) "COUNT(*)"
                               FROM "TC"."EMPLOYEES" "E"
                              WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SU:   Checking validity of unnesting subquery SEL$2 (#0)
SU:     SU bypassed: More than 1 column in connect condition.
SU:     SU bypassed: Failed basic validity checks.
SU:   Validity checks failed.

I believe the SU bypass was introduced by the fix for bug 30593046 - A query having a scalar subquery returned a wrong result (Doc ID 30593046.8).
To demonstrate that, let us add a new department to the DEPARTMENTS table that has no corresponding row in EMPLOYEES:

SQL> insert into departments values (100, 1, 0);

1 row created.

SQL>
SQL> select --+ gather_plan_statistics
  2         *
  3    from departments d
  4   where manager_count > employee_count
  5                         + (select --+ unnest
  6                                   count(*)
  7                              from employees e
  8                             where e.department_id = d.department_id);
  
-- ----------------------------------------------------------------------------------
-- 19.8 - PRODUCES WRONG RESULTS! no rows selected 
-- ----------------------------------------------------------------------------------

no rows selected

SQL> select * from dbms_xplan.display_cursor(format=> 'allstats last');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8gcr6b9xkkqv0, child number 0
-------------------------------------

Plan hash value: 1545815388

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |      1 |        |      0 |00:00:00.01 |      24 |       |       |          |
|*  1 |  HASH JOIN           |             |      1 |      1 |      0 |00:00:00.01 |      24 |  2078K|  2078K|  545K (0)|
|   2 |   VIEW               | VW_SQ_1     |      1 |     10 |     10 |00:00:00.01 |      18 |       |       |          |
|   3 |    HASH GROUP BY     |             |      1 |     10 |     10 |00:00:00.01 |      18 |  1558K|  1558K|  659K (0)|
|   4 |     TABLE ACCESS FULL| EMPLOYEES   |      1 |   1000 |   1000 |00:00:00.01 |      18 |       |       |          |
|   5 |   TABLE ACCESS FULL  | DEPARTMENTS |      1 |     10 |     11 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_0"="D"."DEPARTMENT_ID")
       filter("MANAGER_COUNT">"EMPLOYEE_COUNT"+"COUNT(*)")


27 rows selected.


-- ----------------------------------------------------------------------------------
-- 19.9 - CORRECTLY RETURNS ONE ROW
-- ----------------------------------------------------------------------------------

DEPARTMENT_ID MANAGER_COUNT EMPLOYEE_COUNT
------------- ------------- --------------
          100             1              0

SQL> select * from dbms_xplan.display_cursor(format=> 'allstats last');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8gcr6b9xkkqv0, child number 0
-------------------------------------

Plan hash value: 1015098386

---------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |      1 |00:00:00.01 |     204 |
|*  1 |  FILTER             |             |      1 |        |      1 |00:00:00.01 |     204 |
|   2 |   TABLE ACCESS FULL | DEPARTMENTS |      1 |     10 |     11 |00:00:00.01 |       6 |
|   3 |   SORT AGGREGATE    |             |     11 |      1 |     11 |00:00:00.01 |     198 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES   |     11 |    100 |   1000 |00:00:00.01 |     198 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MANAGER_COUNT">"EMPLOYEE_COUNT"+)
   4 - filter("E"."DEPARTMENT_ID"=:B1)


26 rows selected.

It is worth noting that a similar query that uses one column from DEPARTMENTS has a proper outer join in both 19.8 and 19.9:

SQL> explain plan for
  2  select *
  3    from departments d
  4   where employee_count > (select --+ unnest
  5                                  count(*)
  6                             from employees e
  7                            where e.department_id = d.department_id);

Explained.

SQL> select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 4272783132

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    10 |   350 |    11  (10)| 00:00:01 |
|*  1 |  FILTER               |             |       |       |            |          |
|*  2 |   HASH JOIN OUTER     |             |    10 |   350 |    11  (10)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPARTMENTS |    10 |    90 |     3   (0)| 00:00:01 |
|   4 |    VIEW               | VW_SQ_1     |    10 |   260 |     8  (13)| 00:00:01 |
|   5 |     HASH GROUP BY     |             |    10 |    30 |     8  (13)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| EMPLOYEES   |  1000 |  3000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COUNT(*)" IS NULL AND "EMPLOYEE_COUNT">0 OR
              "EMPLOYEE_COUNT">"COUNT(*)")
   2 - access("ITEM_1"(+)="D"."DEPARTMENT_ID")

20 rows selected.

It made me think that it should be possible to obtain the desired transformation in 19.9 by rewriting the query. Sure enough, I verified that both non-mergeable view or temp table transformation can be used for that:

SQL> explain plan for
  2  with d as (
  3    select --+ no_merge
  4           d.*, manager_count - employee_count employee_diff
  5      from departments d)
  6  select *
  7    from d
  8   where employee_diff > (select --+ unnest
  9                                 count(*)
 10                            from employees e
 11                           where e.department_id = d.department_id);

Explained.

SQL> select * from dbms_xplan.display();

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 1773562247

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    10 |   780 |    11  (10)| 00:00:01 |
|*  1 |  FILTER               |             |       |       |            |          |
|*  2 |   HASH JOIN OUTER     |             |    10 |   780 |    11  (10)| 00:00:01 |
|   3 |    VIEW               |             |    10 |   520 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | DEPARTMENTS |    10 |    90 |     3   (0)| 00:00:01 |
|   5 |    VIEW               | VW_SQ_1     |    10 |   260 |     8  (13)| 00:00:01 |
|   6 |     HASH GROUP BY     |             |    10 |    30 |     8  (13)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| EMPLOYEES   |  1000 |  3000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COUNT(*)" IS NULL AND "EMPLOYEE_DIFF">0 OR
              "EMPLOYEE_DIFF">"COUNT(*)")
   2 - access("ITEM_1"(+)="D"."DEPARTMENT_ID")

21 rows selected.

Conclusion

As Jonathan Lewis remarked there are usually certain reasons for such changes of the existing optimizer behavior:

Various possibilties.
a) Oracle Corp. has realised that there's a boundary condition with this pattern that could produce wrong results and blocks the transformation (e.g. are either of lastmoddate and lastmodtime declared not null - if not the unnest ought to be invalid)
b) Oracle Corp. has modified the optimizer code to produced more efficient plans in almost all cases, but the change introduces certain restrictiions that your SQL now meets (e.g. even if both lastmoddate and lastmodetime the code may now assume that to_char() or to_date() could produce a null from a non-null.
c) Some relatively simple code change has introduced a bug

Although it is quite unfortunate there is no fix control or any other backout switching bringing back the old behavior (as far as I am aware), Oracle actually introduced a fix for the wrong results issue. It might be the lesser of two evils.
The script to this post is available in my GitHub repo.

CURSOR Expressions Return Wrong Results in 18c/19c

That is a wrong results issue I stumbled upon this morning. A developer told me that one of their queries started returning wrong results after we upgraded one of our databases from 12.2.0.1 to 19.7. I constructed a simplified test case to demonstrate this issue which I ran on 19.4. I have been able to reproduce it on 18.3, 19.7, and Live SQL as well.

SQL> create table t1(id int);

Table created.

SQL> create table t2(id int);

Table created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL> insert into t2 values (1);

1 row created.

In a nutshell, I created two tables that have one row with a numeric 1 value. Let's now run the following query:

SQL> select query_type
  2    from (select 'A' query_type
  3            from t1 t11
  4           where exists (
  5                   select null
  6                     from t2 t21
  7                    where t21.id = t11.id)
  8           union all
  9          select 'B' query_type
 10            from t1 t12
 11           where not exists (
 12                   select null
 13                     from t2 t22
 14                    where t22.id = t12.id)
 15         );

Q
-
A

Then, I am going to put that query into a CURSOR expression:

SQL> select cursor(
  2           select query_type
  3             from (select 'A' query_type
  4                     from t1 t11
  5                    where exists (
  6                            select null
  7                              from t2 t21
  8                             where t21.id = t11.id)
  9                    union all
 10                   select 'B' query_type
 11                     from t1 t12
 12                    where not exists (
 13                            select null
 14                              from t2 t22
 15                             where t22.id = t12.id)
 16                  )
 17         ) data
 18    from dual;

DATA
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

Q
-
B

That is when it gets interesting. Somehow the query which returned 'A' being run separately starts returning 'B' after it comes as an input subquery into a CURSOR expression.

I initially started looking at the execution plan in an attempt to figure out what changed in the execution plan between 12.2 and 19c. There are some changes but nothing that drew my eye.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2197603499

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |       |    14   (0)| 00:00:01 |
|   1 |  VIEW                |      |     2 |     6 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
|   9 |  FAST DUAL           |      |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1 / from$_subquery$_001@SEL$2
   2 - SET$1
   3 - SEL$3
   4 - SEL$3 / T11@SEL$3
   5 - SEL$4 / T21@SEL$4
   6 - SEL$5
   7 - SEL$5 / T12@SEL$5
   8 - SEL$6 / T22@SEL$6
   9 - SEL$1 / DUAL@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6" "T22"@"SEL$6")
      FULL(@"SEL$4" "T21"@"SEL$4")
      PQ_FILTER(@"SEL$3" SERIAL)
      FULL(@"SEL$3" "T11"@"SEL$3")
      PQ_FILTER(@"SEL$5" SERIAL)
      FULL(@"SEL$5" "T12"@"SEL$5")
      NO_ACCESS(@"SEL$2" "from$_subquery$_001"@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter( EXISTS (SELECT 0 FROM "T2" "T21" WHERE "T21"."ID"=:B1))
   5 - filter("T21"."ID"=:B1)
   6 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T22" WHERE
              "T22"."ID"=:B1))
   8 - filter("T22"."ID"=:B1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

68 rows selected.

The gather_plan_statistics hint was not really helpful as it is a nested cursor, so that it does not show the rowsource statistics from that level - the nested cursor runs separately and its rowsource statistics are not included in the top-level cursor ones:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |
|   1 |  VIEW                |      |      0 |      2 |      0 |00:00:00.01 |
|   2 |   UNION-ALL          |      |      0 |        |      0 |00:00:00.01 |
|*  3 |    FILTER            |      |      0 |        |      0 |00:00:00.01 |
|   4 |     TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
|*  5 |     TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |
|*  6 |    FILTER            |      |      0 |        |      0 |00:00:00.01 |
|   7 |     TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
|*  8 |     TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |
|   9 |  FAST DUAL           |      |      1 |      1 |      1 |00:00:00.01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter( IS NOT NULL)
   5 - filter("T21"."ID"=:B1)
   6 - filter( IS NULL)
   8 - filter("T22"."ID"=:B1)

I found the explanation of this issue in the SQL trace file:

=====================
PARSING IN CURSOR #139973723001152 len=579 dep=0 uid=78 oct=3 lid=78 tim=4622189819 hv=2185287422 ad='68d3c950' sql
id='0mkkpha141pry'
select cursor(
         select query_type
           from (select 'A' query_type
                   from t1 t11
                  where exists (
                          select null
                            from t2 t21
                           where t21.id = t11.id)
                  union all
                 select 'B' query_type
                   from t1 t12
                  where not exists (
                          select null
                            from t2 t22
                           where t22.id = t12.id)
                )
       ) data
  from dual
END OF STMT
PARSE #139973723001152:c=11048,e=12511,p=0,cr=45,cu=12,mis=1,r=0,dep=0,og=1,plh=2197603499,tim=4622189819
EXEC #139973723001152:c=13,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2197603499,tim=4622189873
=====================
PARSING IN CURSOR #139973720520408 len=272 dep=2 uid=78 oct=3 lid=78 tim=4622190302 hv=3362864010 ad='68d141b0' sqlid='faj0udr472fwa'
SELECT "A2"."QUERY_TYPE" "QUERY_TYPE" 
  FROM  ( 
          (SELECT 'A' "QUERY_TYPE" 
             FROM "T1" "A5"
            WHERE  EXISTS (
                     SELECT 0 
                       FROM "T2" "A6"
                      WHERE "A6"."ID"=:CV1$))
           UNION ALL
          (SELECT 'B' "QUERY_TYPE" 
             FROM "T1" "A4"
            WHERE  NOT EXISTS (
                     SELECT 0 
                       FROM "T2" "A7"
                      WHERE "A7"."ID"=:CV2$))) "A2"
END OF STMT
PARSE #139973720520408:c=375,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=4622190302
..skip..
BINDS #139973720520408:

 Bind#0
  oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=11 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f4e2be3b278  bln=22  avl=00  flg=05
 Bind#1
  oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=11 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f4e2be3b290  bln=22  avl=00  flg=01
EXEC #139973720520408:c=9732,e=9360,p=0,cr=37,cu=0,mis=1,r=0,dep=2,og=1,plh=489056501,tim=4622199711
FETCH #139973723001152:c=10218,e=9845,p=0,cr=37,cu=0,mis=0,r=1,dep=0,og=1,plh=2197603499,tim=4622199753
STAT #139973723001152 id=1 cnt=0 pid=0 pos=1 obj=0 op='VIEW  (cr=0 pr=0 pw=0 str=0 time=0 us cost=12 size=6 card=2)'
STAT #139973723001152 id=2 cnt=0 pid=1 pos=1 obj=0 op='UNION-ALL  (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #139973723001152 id=3 cnt=0 pid=2 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #139973723001152 id=4 cnt=0 pid=3 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=5 cnt=0 pid=3 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=6 cnt=0 pid=2 pos=2 obj=0 op='FILTER  (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #139973723001152 id=7 cnt=0 pid=6 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=8 cnt=0 pid=6 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=9 cnt=1 pid=0 pos=2 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=0 us cost=2 size=0 card=1)'
FETCH #139973720520408:c=178,e=177,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=1,plh=489056501,tim=4622200599
STAT #139973720520408 id=1 cnt=1 pid=0 pos=1 obj=0 op='VIEW  (cr=9 pr=0 pw=0 str=1 time=179 us cost=12 size=6 card=2)'
STAT #139973720520408 id=2 cnt=1 pid=1 pos=1 obj=0 op='UNION-ALL  (cr=9 pr=0 pw=0 str=1 time=177 us)'
STAT #139973720520408 id=3 cnt=0 pid=2 pos=1 obj=0 op='FILTER  (cr=1 pr=0 pw=0 str=1 time=8 us)'
STAT #139973720520408 id=4 cnt=0 pid=3 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=0 card=1)'
STAT #139973720520408 id=5 cnt=0 pid=3 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=1 pr=0 pw=0 str=1 time=6 us cost=3 size=13 card=1)'
STAT #139973720520408 id=6 cnt=1 pid=2 pos=2 obj=0 op='FILTER  (cr=8 pr=0 pw=0 str=1 time=164 us)'
STAT #139973720520408 id=7 cnt=1 pid=6 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 str=1 time=144 us cost=3 size=0 card=1)'
STAT #139973720520408 id=8 cnt=0 pid=6 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=1 pr=0 pw=0 str=1 time=9 us cost=3 size=13 card=1)'
FETCH #139973723001152:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2197603499,tim=4622200901
CLOSE #139973720520408:c=3,e=2,dep=1,type=0,tim=4622201312
CLOSE #139973723001152:c=6,e=193,dep=0,type=0,tim=4622201500

That is where the most remarkable part happens. The subquery is modified in an awfully wrong way: the columns of the outer queries disappear from the inner queries, and they are replaced with some bind variables: CV1$, CV2$.
Those variables, which are of the NUMBER data type, are set to NULL.
Then, the 'A' branch of the UNION ALL, which uses EXISTS, returns nothing. The 'B' branch, which uses NOT EXISTS, returns the 'B' row since the NOT EXISTS subquery brings back an empty result set.

At this stage the root cause of the wrong results issue is known. Obviously, I have no clue why those variables were introduced but I know why that query returns that result.

I tried to tinker around with that query a little bit to see what can be done to make it work, and here is a short summary of my findings:

• Correlated subqueries are essential. The query starts returning the correct results (one 'A' row) once I decorrelate the subqueries:
  

  SQL> select cursor(
    2           select query_type
    3             from (select 'A' query_type
    4                     from t1 t11
    5                    where id in (
    6                            select id
    7                              from t2 t21)
    8                    union all
    9                   select 'B' query_type
   10                     from t1 t12
   11                    where id not in (
   12                            select id
   13                              from t2 t22)
   14                  )
   15         ) data
   16    from dual;
  
  DATA
  --------------------
  CURSOR STATEMENT : 1
  
  CURSOR STATEMENT : 1
  
  Q
  -
  A
      

• Nesting of the subqueries does matter. For instance, the query returns the correct results after I reduce the nesting level:

  SQL> select cursor(
    2           select 'A' query_type
    3             from t1 t11
    4            where exists (
    5                    select null
    6                      from t2 t21
    7                     where t21.id = t11.id)
    8            union all
    9           select 'B' query_type
   10             from t1 t12
   11            where not exists (
   12                    select null
   13                      from t2 t22
   14                     where t22.id = t12.id)
   15         ) data
   16    from dual;
  
  DATA
  --------------------
  CURSOR STATEMENT : 1
  
  CURSOR STATEMENT : 1
  
  Q
  -
  A
  

Based on how Oracle modifies the subquery under the hood, certain CURSOR expressions can return:

• Less rows:

  SQL> doc
  DOC>##############################
  DOC>   Less rows
  DOC>##############################
  DOC>#
  SQL>
  SQL> select cursor(
    2           select query_type
    3             from (select 'A' query_type
    4                     from t1 t11
    5                    where exists (
    6                            select null
    7                              from t2 t21
    8                             where t21.id = t11.id)
    9                    union all
   10                   select 'B' query_type
   11                     from t1 t12
   12                    where exists (
   13                            select null
   14                              from t2 t22
   15                             where t22.id = t12.id)
   16                  )
   17         ) data
   18    from dual;
  
  DATA
  --------------------
  CURSOR STATEMENT : 1
  
  CURSOR STATEMENT : 1
  
  no rows selected
  
  
  SQL>
  SQL> doc
  DOC>   The cursor subquery
  DOC>#
  SQL> select query_type
    2    from (select 'A' query_type
    3            from t1 t11
    4           where exists (
    5                   select null
    6                     from t2 t21
    7                    where t21.id = t11.id)
    8           union all
    9          select 'B' query_type
   10            from t1 t12
   11           where exists (
   12                   select null
   13                     from t2 t22
   14                    where t22.id = t12.id)
   15         )
   16  ;
  
  Q
  -
  A
  B
      

• More rows:

  SQL> doc
  DOC>##############################
  DOC>   More rows
  DOC>##############################
  DOC>#
  SQL>
  SQL> select cursor(
    2           select query_type
    3             from (select 'A' query_type
    4                     from t1 t11
    5                    union all
    6                   select 'B' query_type
    7                     from t1 t12
    8                    where not exists (
    9                            select null
   10                              from t2 t22
   11                             where t22.id = t12.id)
   12                  )
   13         ) data
   14    from dual;
  
  DATA
  --------------------
  CURSOR STATEMENT : 1
  
  CURSOR STATEMENT : 1
  
  Q
  -
  A
  B
  
  
  SQL>
  SQL> doc
  DOC>   The cursor subquery
  DOC>#
  SQL> select query_type
    2    from (select 'A' query_type
    3            from t1 t11
    4           union all
    5          select 'B' query_type
    6            from t1 t12
    7           where not exists (
    8                   select null
    9                     from t2 t22
   10                    where t22.id = t12.id)
   11         );
  
  Q
  -
  A
      

• Different values as it was demostrated in the beginning of this post

The good news is that it is a known documented issue: Bug 30528947 - Nested Query Using CURSOR Expression Returns Wrong Result (Doc ID 30528947.8). There are patches available for some release updates.
The bad news is that the issue is not mentioned on the document I would expect it to be: Things to Consider to Avoid Prominent Wrong Result Problems on 19C Proactively (Doc ID 2606585.1). How is one supposed to avoid such issues? I always read those 'things to consider' before I devise any upgrade plans.
The scripts, a sample trace file, and the spool file for this post are available on: GitHub.

Bug 26588069 - Wrong Result (Duplicate Rows Produced) Using Group By After Upgrading To 12.2.0.1

I have stumbled upon this bug after upgrading one of my databases to 12.2.0.1.
Here is a simplified test case:

SQL> create table gby_elim(x varchar2(10) primary key);

Table created.

SQL> insert into gby_elim values ('a');

1 row created.

SQL> insert into gby_elim values ('b');

1 row created.

SQL>
SQL> select length(x), count(*)
  2    from gby_elim
  3   group by length(x);

 LENGTH(X)   COUNT(*)
---------- ----------
         1          1
         1          1

The last query should have produced 1 row but it returned 2 rows.
Notice how Oracle eliminated the GROUP BY, which it should not have done:

SQL> #0 explain plan for
SQL> /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1635879943

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     2 |    14 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| GBY_ELIM |     2 |    14 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

The SQL Optimizer trace file:

QB before group-by removal:******* UNPARSED QUERY IS *******
SELECT LENGTH("GBY_ELIM"."X") "LENGTH(X)",COUNT(*) "COUNT(*)" FROM "TC"."GBY_ELIM" "GBY_ELIM" GROUP BY LENGTH("GBY_ELIM"."X")
QB before group-by elimination:******* UNPARSED QUERY IS *******
SELECT LENGTH("GBY_ELIM"."X") "LENGTH(X)",COUNT(*) "COUNT(*)" FROM "TC"."GBY_ELIM" "GBY_ELIM" GROUP BY LENGTH("GBY_ELIM"."X")
Registered qb: SEL$47952E7A 0x88af59e8 (ELIMINATION OF GROUP BY SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$47952E7A nbfros=1 flg=0
    fro(0): flg=0 objn=261334 hint_alias="GBY_ELIM"@"SEL$1"

QB after group-by elimination:******* UNPARSED QUERY IS *******
SELECT LENGTH("GBY_ELIM"."X") "LENGTH(X)",1 "COUNT(*)" FROM "TC"."GBY_ELIM" "GBY_ELIM"
Registered qb: SEL$9BB7A81A 0x88af59e8 (ELIMINATION OF GROUP BY SEL$47952E7A; SEL$47952E7A)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$9BB7A81A nbfros=1 flg=0
    fro(0): flg=0 objn=261334 hint_alias="GBY_ELIM"@"SEL$1"

QB after group-by removal:******* UNPARSED QUERY IS *******
SELECT LENGTH("GBY_ELIM"."X") "LENGTH(X)",1 "COUNT(*)" FROM "TC"."GBY_ELIM" "GBY_ELIM"

The plan table from the trace file:

----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     2 |           |
| 1   |  TABLE ACCESS FULL | GBY_ELIM|     2 |    14 |     2 |  00:00:01 |
-------------------------------------+-----------------------------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$9BB7A81A         / GBY_ELIM@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------

Content of other_xml column
===========================
  db_version     : 12.2.0.1
  parse_schema   : TC
  dynamic_sampling: 2
  plan_hash_full : 619495063
  plan_hash      : 1635879943
  plan_hash_2    : 619495063
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$9BB7A81A")
      ELIM_GROUPBY(@"SEL$47952E7A")
      OUTLINE(@"SEL$47952E7A")
      ELIM_GROUPBY(@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$9BB7A81A" "GBY_ELIM"@"SEL$1")
    END_OUTLINE_DATA
  */

This is Bug 26588069 - Wrong Result (Duplicate Rows Produced) Using Group By After Upgrading To 12.2.0.1 (Doc ID 26588069.8) and any workaround mentioned in the aforementioned document will work. To demonstrate a few:

SQL> select /*+ opt_param('_fix_control' '23210039:0')*/
  2         length(x), count(*)
  3    from gby_elim
  4   group by length(x);

 LENGTH(X)   COUNT(*)
---------- ----------
         1          2

SQL>
SQL> select /*+ opt_param('_optimizer_aggr_groupby_elim' 'false')*/
  2         length(x), count(*)
  3    from gby_elim
  4   group by length(x);

 LENGTH(X)   COUNT(*)
---------- ----------
         1          2

I can also add my own workaround using the NO_ELIM_GROUPBY hint:

SQL> select /*+ no_elim_groupby(@sel$1)*/
  2         length(x), count(*)
  3    from gby_elim
  4   group by length(x);

 LENGTH(X)   COUNT(*)
---------- ----------
         1          2

The patch for this bug is also available for most platforms.

Index full scan min/max scans returning wrong results on partitioned tables

A developer has recently shown me an interesting wrong results issue in one of our 12.1.0.2 databases.
Having investigated it, I identified the root cause of that issue and constructed a simple test case that can be used to reproduce it.
Let's setup some sample data - a list-partitioned table with just two rows and a global index:

SQL> create table t (
  2    id,
  3    part_key,
  4    constraint t_pk primary key(id))
  5  partition by list(part_key) (
  6    partition values ('KEY1'),
  7    partition values ('KEY2'))
  8  as
  9  select 1, 'KEY1' from dual union all
 10  select 2, 'KEY2' from dual;

Table created.

SQL>
SQL> col part_key for a8
SQL> select *
  2    from t;

        ID PART_KEY
---------- --------
         1 KEY1
         2 KEY2

Here are queries returning wrong results:

SQL> select max(id)
  2    from t
  3   where part_key = 'KEY1';

   MAX(ID)
----------
         2

SQL> select min(id)
  2    from t
  3   where part_key = 'KEY2';

   MIN(ID)
----------
         1

They should have returned 1 and 2 correspondingly. Things definitely went awry.
In such a scenario, I always try to figure out how exactly the optimizer comes up with the result.
The execution plan is a good place to start:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2094033419

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    19 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |    19 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_PK |     1 |    19 |            |          |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

13 rows selected.

Aha! There is no way for the optimizer to answer that query by using that execution plan.
Where is the PART_KEY predicate? It seems to have been completely lost there.
A quick search on MOS got me a candidate issue: Bug 22913528 - wrong results with partition pruning and min/max scans (Doc ID 22913528.8)
That document mentions a workaround which I successfully implemented:

SQL> select /*+ opt_param('_fix_control' '16346018:0')*/
  2         max(id)
  3    from t
  4   where part_key = 'KEY1';

   MAX(ID)
----------
         1

SQL>

The plan of that query is below:

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2831600127

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    19 |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE        |      |     1 |    19 |            |          |       |       |
|   2 |   PARTITION LIST SINGLE|      |     1 |    19 |     2   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS FULL   | T    |     1 |    19 |     2   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

14 rows selected.

The MOS document says that this issue has been fixed in the 12.1.0.2.190115 (Jan 2019) Database Proactive Bundle Patch.

Bug 20214168 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present

I started to use Oracle 12c in the early 2014 in non-production environments.
At now I am extensively testing the ADO, In-Memory functionalities and have couple of database in production.
They already use DBBP (Database Bundle Patch) 10 for engineering systems and database In-Memory and have several underscope parameters in effect.
Unfortunately, a few days ago we have faced with the wrong results caused by the Bug 20214168 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present.
This problem was already discussed on the freelist oracle-l thread Strange Behaviour (with Test Case).
Original thread was Exadata specific.
We dont have an Exadata yet but we use DBIM (Database In-Memory) and so we install DBBP patches.
I found out that solution from the thread with event 10055 don't help to resolve wrong results issue in our environment.
Let's create test table and data from the original bug:

SQL> create table test_fact(chrtype varchar2(3), rate number);
SQL> insert into test_fact values('R03', 1.3);
SQL> insert into test_fact values('LDU', 0.21);
SQL> 
SQL> select *
  2    from test_fact;

CHRTYPE         RATE
--------- ----------
R03              1.3
LDU              .21

Now execute below query:

SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041

Notice wrong results. Above query should return 0.151 and not 0.041.
Now we will execute same query with the event 10055 set:

SQL> alter session set events '10055 trace name context forever, level 0x200';
SQL> 
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041

Notice that results are the same (they are wrong) and the event 10055 set to level 0x200 dont help.
I observed these results in the environment with Patch 21188742 - Database Patch for Engineered Systems and DB In-Memory 12.1.0.2.10 (Jul2015) applied.
May be other levels of event 10055 could help?

[oracle@localhost ~]$ oerr ora 10055
10055, 00000, "Rowsets: turn off rowsets for various operations"
// *Document: NO
// *Cause:    N/A
// *Action:   Turns off rowsets for various operations
//            Level:
//            0x00000001 - turn off for table scan
//            0x00000002 - turn off for hash join consume
//            0x00000004 - turn off for hash join produce
//            0x00000008 - turn off for group by
//            0x00000010 - turn off for sort
//            0x00000020 - turn off for table-queue out
//            0x00000040 - turn off for table-queue in
//            0x00000080 - turn off for identity
//            0x00000100 - turn off for granule iterator
//            0x00000200 - turn off for EVA functions
//            0x00000400 - turn off for PL/SQL
//            0x00000800 - turn off for upgrade
//            0x00001000 - turn off for database startup
//            0x00002000 - turn off for blobs and clobs
//            0x00004000 - turn off for tracing row source
//            0x00008000 - turn off rowset information in explain plan
//            0x00010000 - disable hash join rowsets fast path
//            0x00020000 - turn off for bloom create
//            0x00040000 - turn off for bloom use
//            0x00080000 - disable prefetch for hash join
//            0x00100000 - disable prefetch for bloom
//            0x00200000 - disable semi blocking hash join
//            0x00400000 - turn off rowset for fixed table
//

Unfortunately, other levels also have not helped.
I found 2 possible solutions for this problem case.
1. set _rowsets_enabled to false:

SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041
SQL> 
SQL> alter session set "_rowsets_enabled"=false;
SQL> 
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .151

2. set _rowsets_max_rows to 1:

SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .041
SQL> 
SQL> alter session set "_rowsets_max_rows"=1;
SQL> 
SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact;

    RESULT
----------
      .151

Off course, we shouldn't use underscope parameters without Oracle Support agreement.
Instead we should apply the fix for bug 20214168 ASAP.
I tried to apply the patch 20214168 to this Oracle Home but OPatch informed me that patch already applied!

Recommended actions: The fixes by this patch are currently in the Oracle Home. There is no need to apply this patch.
Patch : 20214168

        Bug SubSet of 21125181
        Subset bugs are:
        20214168

From the opatch lsinv I saw that definitely fix for bug 20214168 already installed:

Local Machine Information::
Hostname: localhost
ARU platform id: 23
ARU platform description:: Solaris Operating System (SPARC 64-bit)
                
Installed Top-level Products (1): 
Oracle Database 12c                                                  12.1.0.2.0There are 1 products installed in this Oracle Home.
Interim patches (5) :
Patch  20831113     : applied on Wed Aug 05 15:01:24 NOVT 2015
Unique Patch ID:  18927529
Patch description:  "OCW Patch Set Update : 12.1.0.2.4 (20831113)"
   Created on 23 Jun 2015, 06:58:08 hrs UTC
   Bugs fixed:
     18589889, 19139608, 19280860, 19061429, 19133945, 19341538, 20011424
...skip...
Patch  21125181     : applied on Wed Aug 05 14:50:35 NOVT 2015
Unique Patch ID:  19005983
Patch description:  "DATABASE BUNDLE PATCH: 12.1.0.2.10 (21125181)"
   Created on 1 Jul 2015, 22:01:24 hrs PST8PDT
Sub-patch  20594149; "DATABASE BUNDLE PATCH: 12.1.0.2.7 (20594149)"
Sub-patch  20415006; "DATABASE BUNDLE PATCH: 12.1.0.2.6 (20415006)"
Sub-patch  20243804; "DATABASE BUNDLE PATCH: 12.1.0.2.5 (20243804)"
   Bugs fixed:
...skip...
     19990543, 19012044, 20214168, 20209481, 18885870, 13640676, 13498243
...skip...
Patch  19396455     : applied on Mon Jul 13 17:43:01 NOVT 2015
Unique Patch ID:  18154832
   Created on 15 Oct 2014, 20:19:28 hrs PST8PDT
   Bugs fixed:
     19396455
Patch  19567916     : applied on Mon Jul 13 17:41:37 NOVT 2015
Unique Patch ID:  18878751
   Created on 1 May 2015, 01:31:51 hrs PST8PDT
   Bugs fixed:
     19567916
Patch  20879889     : applied on Mon Jul 13 17:33:26 NOVT 2015
Unique Patch ID:  18969474
   Created on 27 May 2015, 10:30:29 hrs PST8PDT
   Bugs fixed:
     20879889

I have found that bug 21214168 incorrectly informed as fixed but actually it's not fixed: Bug 21553476 - Wrong Results using aggregations of CASE expression with fix of bug 20003240 present in Exadata (Doc ID 21553476.8).
So instead of install patch 21214168, we should install Patch 21553476: EXADATA X5-2 RESULTS WRONG NUMERIC CALCULATION.
I had installed patch 21553476 and problem was resolved after that!

SQL> select sum(
  2           case
  3             when chrtype in ('R03', 'LDU')
  4             then rate/10
  5           end) result
  6    from test_fact
  7  /

    RESULT
----------
      .151