Oracle Scratchpad

August 6, 2020

Case and Aggregate bug

Filed under: 12c,Bugs,Oracle,Troubleshooting,Upgrades — Jonathan Lewis @ 12:43 pm BST Aug 6,2020

The following description of a bug appeared on the Oracle Developer Community forum a little while ago – on an upgrade from 12c to 19c a query starting producing the wrong results on a simple call to the average() function. In fact it turned out to be a bug introduced in 12.2.0.1.

The owner of the thread posted a couple of zip files to build a test case – but I had to do a couple of edits, and change the nls_numeric_characters to ‘,.’ in order to get past a formatting error on a call to the to_timestamp() function. I’ve stripped the example to a minimum, and translated column name from German (which was presumably the source of the nls_numeric_characters issue) to make it easier to demonstrate and play with the bug.

First the basic data – you’ll notice that I’ve tested this on 12.1.0.2, 12.2.0.1 and 19.3.0.0 to find out when the bug appeared:

rem
rem     Script:         case_aggregate_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2020
rem     Purpose:        
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem

create table test(
        case_col        varchar2(11), 
        duration        number(*,0), 
        quarter         varchar2(6), 
        q2h_knum_b      varchar2(10)
   )
/

insert into test values('OK',22,'1.2020','AB1234');
insert into test values('OK',39,'1.2020','AB1234');
insert into test values('OK',30,'1.2020','AB1234');
insert into test values('OK',48,'1.2020','AB1234');
commit;

execute dbms_stats.gather_table_stats(user,'test')

create or replace force view v_test
as 
select 
        q2h_knum_b,
        case 
                when b.case_col not like 'err%'
                        then b.duration 
        end     duration,
        case 
                when b.case_col not like 'err%' 
                        then 1 
                        else 0 
        end     status_ok
from
        test b
where
        substr(b.quarter, -4) = 2020
;


break on report
compute avg of duration on report
select * from v_test;

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

Q2H_KNUM_B   DURATION  STATUS_OK
---------- ---------- ----------
AB1234             22          1
AB1234             39          1
AB1234             30          1
AB1234             48          1
           ----------
avg             34.75


I’ve created a table, loaded some data, gathered stats, then created a view over the table. The view includes a couple of columns that use a simple case expression, and both expressions are based in the same way on the same base column (this may, or may not, be significant in what’s coming). I’ve then run off a simple query with a couple of SQL*Plus commands to report the actual content of the view with the average of the duration column – which is 34.75.

So now we run a couple of queries against the view which aggregate the data down to a single row – including the avg() of the duration – using the coalesce() function – rather than the older nvl() function – to convert any nulls to zero.


select
        coalesce(count(duration), 0)    duration_count,
        coalesce(median(duration), 0)   duration_med,
        coalesce(avg(duration), 0)      duration_avg,
        coalesce(sum(status_ok), 0)     ok_count
from
        v_test  v1
where
        instr('AB1234', q2h_knum_b) > 0
/

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

DURATION_COUNT DURATION_MED DURATION_AVG   OK_COUNT
-------------- ------------ ------------ ----------
             4         34.5            0          4

You’ll notice that the duration_avg is reported as zero (this would be the same if I used nvl(), and would be a null if I omitted the coalesce(). This is clearly incorrect. This was the output from 19.3; 12.2 gives the same result, 12.1.0.2 reports the average correctly as 34.75.

There are several way in which you can modify this query to get the right average – here’s one, just put the ok_count column first in the select list:


select
        coalesce(sum(status_ok), 0)     ok_count,
        coalesce(count(duration), 0)    duration_count,
        coalesce(median(duration), 0)   duration_med,
        coalesce(avg(duration), 0)      duration_avg
from
        v_test  v1
where
        instr('AB1234', q2h_knum_b) > 0
/

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

  OK_COUNT DURATION_COUNT DURATION_MED DURATION_AVG
---------- -------------- ------------ ------------
         4              4         34.5        34.75


There’s no obvious reason why the error should occur, but there’s a little hint about what may be happening in the Column projection information from the execution plan. The basic plan is the same in both cases, so I’m only show it once; but it’s followed by two versions of the projection information (restricted to operation 1) which I’ve formatted to improve:

Plan hash value: 2603667166

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     2 (100)|          |
|   1 |  SORT GROUP BY     |      |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    20 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter((INSTR('AB1234',"Q2H_KNUM_B")>0 AND
              TO_NUMBER(SUBSTR("B"."QUARTER",(-4)))=2020))

Column Projection Information (Operation 1 only):  (Wrong result)
-----------------------------------------------------------------
PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22],
COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22]



Column Projection Information (Operation 1 only):  (Right result)
-----------------------------------------------------------------
PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22],
COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END)[22], 
SUM  (CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END)[22]

As you can see, to report avg() Oracle has projected sum() and count().

When we get the right result the sum() for duration appears immediately after the count().

When we get the wrong result the sum() for ok_count comes between the count() and sum() for duration.

This makes me wonder whether Oracle is somehow just losing track of the sum() for duration and therefore dividing null by the count().

This is purely conjecture, of course, and may simply be a coincidence – particularly since 12.1.0.2 gets the right result and shows exactly the same projection information.

Readers are left to experiment with other variations to see if they can spot other suggestive patterns.

Update (Aug 2020)

This is now logged as Bug 31732779 – WRONG RESULT WITH CASE STATEMENT AGGREGATION , though it’s not yet publicly visible.

2 Comments »

  1. Hello Jonathan,

    Very interesting and a little intriguing case !

    Just as a curiosity, I tried to see what happens if I use an analytics aggregate, keeping the order of the columns
    the same as in the original SELECT.

    Surprisingly, the result here is correct, using LiveSQL (19.5.0.0.0):

    select  duration,
            coalesce(count(duration) over(), 0)    duration_count,
            coalesce(median(duration) over(), 0)   duration_med,
            coalesce(avg(duration) over(), 0)      duration_avg,        
            coalesce(sum(status_ok) over(), 0)     ok_count
    from
            v_test  v1
    where
            instr('AB1234', q2h_knum_b) > 0
    /
    
    DURATION	DURATION_COUNT	DURATION_MED	DURATION_AVG	OK_COUNT
    ------------------------------------------------------------------------
    22		4		34.5		34.75		4
    30		4		34.5		34.75		4
    39		4		34.5		34.75		4
    48		4		34.5		34.75		4
    
    4 rows selected.
    

    And, if we look at the projected columns, we see that the aggregate for column OK_COUNT appears first,
    and the aggregate for AVG was also projected “as is”, unlike in the case of the wrong result:

    
    Plan hash value: 2901716889
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
    |   1 |  WINDOW SORT       |      |     1 |    20 |     4  (25)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| TEST |     1 |    20 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter((INSTR('AB1234',"Q2H_KNUM_B")>0 AND 
                  TO_NUMBER(SUBSTR("B"."QUARTER",(-4)))=2020))
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=1) CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 
           "B"."DURATION" END [22], "B"."CASE_COL"[VARCHAR2,11], 
           "B"."DURATION"[NUMBER,22], "B"."QUARTER"[VARCHAR2,6], 
           "Q2H_KNUM_B"[VARCHAR2,10], 
           SUM(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END ) OVER ()[22], 
           AVG(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22], 
           PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22], 
           COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22]
    
       2 - "B"."CASE_COL"[VARCHAR2,11], "B"."DURATION"[NUMBER,22], 
           "B"."QUARTER"[VARCHAR2,6], "Q2H_KNUM_B"[VARCHAR2,10]
     
    

    And, even if we explicitly add another column for analytic SUM, the AVG still appears in the projection,
    though SUM and COUNT would have sufficed to calculate it:

    
    select  duration,
            coalesce(count(duration) over(), 0)    duration_count,
            coalesce(median(duration) over(), 0)   duration_med,
            coalesce(avg(duration) over(), 0)      duration_avg,
            coalesce(sum(duration) over(), 0)      duration_sum,        
            coalesce(sum(status_ok) over(), 0)     ok_count
    from
            v_test  v1
    where
            instr('AB1234', q2h_knum_b) > 0
    /
    
    DURATION	DURATION_COUNT	DURATION_MED	DURATION_AVG	DURATION_SUM	OK_COUNT
    -------------------------------------------------------------------------------------------
    22		4		34.5		34.75		139		4
    30		4		34.5		34.75		139		4
    39		4		34.5		34.75		139		4
    48		4		34.5		34.75		139		4
    
    4 rows selected.
    
    
     
    Plan hash value: 2901716889
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
    |   1 |  WINDOW SORT       |      |     1 |    20 |     4  (25)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| TEST |     1 |    20 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter((INSTR('AB1234',"Q2H_KNUM_B")>0 AND 
                  TO_NUMBER(SUBSTR("B"."QUARTER",(-4)))=2020))
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=1) CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 
           "B"."DURATION" END [22], "B"."CASE_COL"[VARCHAR2,11], 
           "B"."DURATION"[NUMBER,22], "B"."QUARTER"[VARCHAR2,6], 
           "Q2H_KNUM_B"[VARCHAR2,10], 
           SUM(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END ) OVER ()[22], 
           SUM(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22], 
           AVG(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22], 
           PERCENTILE_CONT(.5) WITHIN GROUP ( ORDER BY CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22], 
           COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END ) OVER ()[22]
    
       2 - "B"."CASE_COL"[VARCHAR2,11], "B"."DURATION"[NUMBER,22], 
           "B"."QUARTER"[VARCHAR2,6], "Q2H_KNUM_B"[VARCHAR2,10]
     
    

    Another little oddity is that if you simply remove the MEDIAN aggregate from the original query,
    keeping the other columns in the same order, then the AVG calculation is correct,
    though the aggregate for column OK_COUNT still appears between the COUNT and SUM used for calculating the AVG
    ( same as for the wrong result ):

    
    select
            coalesce(count(duration), 0)    duration_count,
            -- coalesce(median(duration), 0)   duration_med,
            coalesce(avg(duration), 0)      duration_avg,
            coalesce(sum(status_ok), 0)     ok_count
    from
            v_test  v1
    where
            instr('AB1234', q2h_knum_b) > 0
    /
    
    DURATION_COUNT	DURATION_AVG	OK_COUNT
    ----------------------------------------
    4		34.75		4
    
    
    Plan hash value: 1950795681
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |    20 |            |          |
    |*  2 |   TABLE ACCESS FULL| TEST |     1 |    20 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter((INSTR('AB1234',"Q2H_KNUM_B")>0 AND 
                  TO_NUMBER(SUBSTR("B"."QUARTER",(-4)))=2020))
     
    Column Projection Information (identified by operation id):
    -----------------------------------------------------------
     
       1 - (#keys=0) 
           COUNT(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END )[22], 
           SUM(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN 1 ELSE 0 END )[22], 
           SUM(CASE  WHEN "B"."CASE_COL" NOT LIKE 'err%' THEN "B"."DURATION" END )[22]
    
       2 - (rowset=60) "B"."CASE_COL"[VARCHAR2,11], 
           "B"."DURATION"[NUMBER,22]
     
    34 rows selected.
    
    

    Thanks a lot for these so interesting insights :)

    Best Regards,
    Iudith Mentzel

    Comment by Iudith Mentzel — August 6, 2020 @ 2:08 pm BST Aug 6,2020 | Reply

    • Iudith,

      Thanks for supplying a few more examples, and apologies for my slow reply. It will be interesting to see if the bug does get published and if the resolution gives any clues about the error that introduced it.

      Following on from your comment about eliminating the median() from the query – in fact I think if you eliminate any one of the three columns (other than the average) then the result for the average is correct.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — August 17, 2020 @ 11:28 am BST Aug 17,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.