Oracle strange SUM behaviour

Question

I have two queries, which to my understanding should deliver the same result but they do not. Clearly I'm missing here some important point, which I hope you can help me out with.

1. Query (I assume this is the wrong one):

     SELECT SUM(a.amount) AS A_SUM ---10 727.470
     FROM billdetail a
     INNER JOIN bill c
       ON (a.bill_id = c.bill_id)
     INNER JOIN dates d
       ON (c.date_id = d.date_id)
     WHERE d.year                = '2014'
       AND c.status        <> 'D'
       AND a.status        <> 'D';
   

2. Query:

   SELECT SUM(C_SUM) ---10 754.279
   FROM
     (
     SELECT SUM(a.amount) AS C_SUM
     FROM billdetail a
     INNER JOIN bill c
       ON (a.bill_id = c.bill_id)
     INNER JOIN dates d
       ON (c.date_id = d.date_id)
     WHERE d.year                = '2014'
       AND c.status        <> 'D'
       AND a.status        <> 'D'
     GROUP BY c.bill_id
     );
   

As you can see, query 1 gives me 10 727.470 where query 2 gives me 10 754.279, so 27 less.

Can you explain me why this is? I thought I do here the same with both: select only the bills from 2014 and then from there on grab all billdetails of them and sum up the amounts. But clearly I have here some understanding problem I hope you can help me with.

Answer 1

You may be hitting "Bug 4604970 Wrong results with 'hash group by' aggregation enabled". You can avoid that bug by running alter session set "_gby_hash_aggregation_enabled"=false;. Here's a simple test case for the bug, that sadly still exists after all these years.

SQL> select stddev(test), count(distinct test) from
  2  (
  3      select 7/9 test from dual
  4      union all
  5      select 7/9 test from dual
  6  );
select stddev(test), count(distinct test) from
*
ERROR at line 1:
ORA-01428: argument '-.00000000000000000000000000000000000001' is out of range


SQL> alter session set "_gby_hash_aggregation_enabled"=false;

Session altered.

SQL> select stddev(test), count(distinct test) from
  2  (
  3      select 7/9 test from dual
  4      union all
  5      select 7/9 test from dual
  6  );

STDDEV(TEST) COUNT(DISTINCTTEST)
------------ -------------------
           0                   1