11 Replies Latest reply on Aug 6, 2020 3:56 PM by Jonathan Lewis

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

    S-Max

      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?

        • 1. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
          Jonathan Lewis

          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

          • 2. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
            S-Max

            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

            • 3. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
              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.

               

               

              1 person found this helpful
              • 4. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
                S-Max

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

                • 5. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
                  Jonathan Lewis

                  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

                  • 8. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
                    Mark D Powell

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

                    • 10. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
                      Gaz in Oz

                      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> ed
                      Wrote 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) > 0
                      SQL> /
                      
                      DURATION_COUNT DURATION_MED DURATION_AVG   OK_COUNT
                      -------------- ------------ ------------ ----------
                                   4         34.5        34.75          4
                      
                      SQL>
                      

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

                      • 11. Re: Oracle DB 19c: AVG function and wrong results. Already known BUG, or something else?
                        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