Forum Stats

  • 3,733,518 Users
  • 2,246,779 Discussions
  • 7,856,750 Comments

Discussions

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

S-Max
S-Max Member Posts: 208 Bronze Badge

Hi all,

after upgrade from 12c to 19c I have problems with select statements including AVG function.

Without any regularity it brings 0 or correct value, and it is dependent on the place in the select clause and/or selected columns!

If you would like to test this case I have attached 2 files with create table, insert rows, create view and select statements I use.

On the 11g and 12g all select statements are workíng fine!

Anyone any ideas?

TubbyS-Max

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,603 Gold Crown
    edited August 2020

    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

  • S-Max
    S-Max Member Posts: 208 Bronze Badge
    edited August 2020

    Hello Jonathan,

    thank you very much for your answer.

    Probably are all database versions from 12.2 affected. I have had a 12.1. There was no problem. 11g have no problems with AVG too.

    The outputs of explain plans from 11g and 19c are exact the same.

    Here is the explain plan output of the problem statement from 19c:

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

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

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

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

    |  1  |  SORT GROUP BY     |      |    1  |  337  |            |          |

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

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

    Predicate Information (identified by operation id):

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

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

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

    Note

    -----

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

    Here is the explain plan output of the problem statement from 11g:

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

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

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

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

    |   1 |  SORT GROUP BY     |      |     1 |   337 |            |          |

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

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

    Predicate Information (identified by operation id):

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

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

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

    Note

    -----

       - dynamic sampling used for this statement (level=2)

    Regards,

    Leonid Pavlov

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,603 Gold Crown
    edited August 2020

    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.

    TubbyS-Max
  • S-Max
    S-Max Member Posts: 208 Bronze Badge
    edited August 2020

    Thank you, Jonathan!

    Yes, it is dependent on the place of AVG in the select list. It is very strange. I have raised the SR and hope, the support will present any bugfix.

    I will update this thread with new informations...

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,603 Gold Crown
    edited August 2020

    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

    S-Max
  • S-Max
    S-Max Member Posts: 208 Bronze Badge
    edited August 2020

    Hello Jonathan,

    thank you!

    Regards,

    Leonid

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,603 Gold Crown
    edited August 2020
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited August 2020

    S-max, when you get an update from Oracle (bug#, fixed in patch, etc...) please update the thread with the information

  • S-Max
    S-Max Member Posts: 208 Bronze Badge
    edited August 2020

    Yes, I will do it!

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited August 2020

    Using the script on @Jonathan Lewis's site I had a bit of a play around and by adding 0 to the duration column, the result came good:

    SQL> edWrote file afiedt.buf  1  select coalesce(count(duration), 0)   duration_count,  2         coalesce(median(duration), 0)  duration_med,  3         coalesce(avg(duration + 0), 0) duration_avg,  4         coalesce(sum(status_ok), 0)    ok_count  5  from   v_test  v1  6* where  instr('AB1234', q2h_knum_b) > 0SQL> /DURATION_COUNT DURATION_MED DURATION_AVG   OK_COUNT-------------- ------------ ------------ ----------             4         34.5        34.75          4SQL>

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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,603 Gold Crown
    edited August 2020

    @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

Sign In or Register to comment.