Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?

I've just created your table, view and data on 12.2.0.1 and 19.3.0.0 and run both queries against both versions several times.

Both versions gave me the same results every time and opdauer_avg was zero for both queries on every execution.

There's no obvious reason in the execution plans I see that might explain a potential bug, but perhaps you're getting different execution plans between 12c and 19c - can you run the test and pull the execution plans from memory to see if they differ.

set serveroutput off

-- run your query

set linesize 80

set pagesize 60

set trimspool on

select * from table(dbms_xplan.display_cursor);

-------------------------------------------------------------------------------

| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |        |       |       |     3 (100)|          |

|   1 |  SORT GROUP BY       |        |     1 |   389 |            |          |

|   2 |   VIEW               | V_TEST |     4 |  1556 |     3  (34)| 00:00:01 |

|   3 |    WINDOW SORT       |        |     4 |  1348 |     3  (34)| 00:00:01 |

|*  4 |     TABLE ACCESS FULL| TEST   |     4 |  1348 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter((INSTR('AB1234',"B"."Q2H_KNUM_B")>0 AND

              TO_NUMBER(SUBSTR("B"."entlquartal",-4))=2020))

Note

-----

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

Regards

Jonathan Lewis

Leonid,

Ran up a copy of 12.1.0.2 and demonstrated the change - 12.2.0.1 showing zero 12.1.0.2 showing the right answer.

There is no obvious reason why this should be happening, but I have minimised your test case.  With your tables and data:

SELECT

        COALESCE(COUNT(OPDAUER), 0) as "OPDAUER_ANZ",

        COALESCE(MEDIAN(OPDAUER), 0) as "OPDAUER_MED",

        COALESCE(AVG(OPDAUER), 0) as "OPDAUER_AVG",

        COALESCE(SUM(STATUS_OK), 0) as "ENTLGRUND_GES"

FROM    V_TEST  v1

WHERE  INSTR('AB1234', Q2H_KNUM_B) > 0

/

With these 4 colums the average appears as zero.

Eliminate the count(), the median(), or the sum() and you get the right average.

I can't explain why this goes wrong - the only thought I've had is that there's some error in the way Oracle is handling the projection of average. If you take the simpler case:    select avg(opdauer) from v_test;

The plan is the same with 12.1.0.2 and 19.3.0.0, but 12.1 projects

AVG(CASE  WHEN ("B"."Q2H_PRSTATUS_CASE" NOT LIKE 'ERR%' AND "B"."OPDAUER" IS NOT NULL) THEN "B"."OPDAUER" ELSE NULL END)[22]

while 19.3 projects:

COUNT(CASE  WHEN ("B"."Q2H_PRSTATUS_CASE" NOT LIKE 'ERR%' AND "B"."OPDAUER" IS NOT NULL) THEN "B"."OPDAUER" ELSE NULL END)[22],

SUM(CASE  WHEN ("B"."Q2H_PRSTATUS_CASE" NOT LIKE 'ERR%' AND "B"."OPDAUER" IS NOT NULL) THEN "B"."OPDAUER" ELSE NULL END )[22]

There may be something about your select list that breaks Oracle because the expression is a little complex and you're selecting an expression that Oracle would have projected to calculate the average.  This is just speculation, but at least the simpler example may make it easier to get a response when you raise an SR.

Regards

Jonathan Lewis

Complete run from 19.3

SQL> select avg(opdauer) from v_test;

AVG(OPDAUER)

------------

  31.9593496

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'projection'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID  bgckcwmsn88tu, child number 0

-------------------------------------

select avg(opdauer) from v_test

Plan hash value: 1950795681

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |   123 |  3075 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(TO_NUMBER(SUBSTR("B"."entlquartal",(-4)))=2020)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=0) COUNT(CASE  WHEN ("B"."Q2H_PRSTATUS_CASE" NOT LIKE

       'ERR%' AND "B"."OPDAUER" IS NOT NULL) THEN "B"."OPDAUER" ELSE NULL END

       )[22], SUM(CASE  WHEN ("B"."Q2H_PRSTATUS_CASE" NOT LIKE 'ERR%' AND

       "B"."OPDAUER" IS NOT NULL) THEN "B"."OPDAUER" ELSE NULL END )[22]

   2 - (rowset=60) "B"."Q2H_PRSTATUS_CASE"[VARCHAR2,11],

       "B"."OPDAUER"[NUMBER,22]

Note

-----

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

33 rows selected.

UPDATE:

I had another thought - so I moved the sum() column from last to first in the query, and the results changed to show the right answer (in 12.2 and 19.3).  Maybe another clue for Oracle support.

Leonid,

I've stripped your example to a minimum - 4 columns in table, 4 rows, 3 columns in the view, and a demonstration of the error and correction resulting from changing column order.  I'm about to write up a short blog note, which you could point  to in your SR.

I've made some minor changes - translated column names to English, got rid of the lower-case quoted column name, eliminated the "byte" specifier for character columns, and simplified the case expression for duration/opdauer.

Regards

Jonathan Lewis

Posted:

https://jonathanlewis.wordpress.com/2020/08/06/case-and-aggregate-bug/

Regards

Jonathan Lewis

@Gaz in Oz

Using dbms_xplan.display_cursor(format => 'all') shows differences in the predicate part but everything else was pretty much the same.

I think you meant to type projection, not predicate.

This picks up an extra column:

COUNT(CASE  WHEN ("B"."CASE_COL" NOT LIKE 'err%') THEN "B"."DURATION" END +0)[22],

The same happens if you change the code to select count(duration+ 0).

It makes it look as if Oracle's handling of the original query first recognises that it has two uses for projecting  the duration; but then "loses" the location of the projected column when it wants to use it for the average.  When you force an extra appearance with the "+0" Oracle doesn't then have to remember that it's using the same thing twice.

Regards

Jonathan Lewis