GROUP BY – wrong results in 12.1.0.2 – Learning is not a spectator sport

I encountered this during some testing – the optimizer can be a little overzealous when it encounters nested group-by’s

SQL> create table T ( ts number not null, c char(10));
 
Table created.
 
SQL> insert into T
  2  select 100+dbms_random.value(1,50),'x'
  3  from ( select 1 from dual  connect by level < 1000 ),
  4       ( select 1 from dual  connect by level  commit;
 
Commit complete.
 
SQL>
SQL> exec dbms_stats.gather_table_stats('','T') ;
 
PL/SQL procedure successfully completed.

So I’ve got ~1million rows, with column TS containing decimal numbers ranging between 100 to 150. Now first I’m going to reduce those numbers to integers, via an inline view, and then truncate those numbers to the nearest 100 (the outer statement). Because all the numbers are between 100 and 150, rounding down to the nearest 100, should return just a single row. Lets see what happens…

SQL> select trunc(ts/100),
  2         sum(cnt) per_min,
  3         avg(cnt) avg_cnt_per_min,
  4         max(cnt) max_cnt
  5  from
  6     ( select cast(ts as int) ts,
  7               count(*) cnt
  8        from   t
  9        group by cast(ts as int)
10      )
11  group by trunc(ts/100)
12  order by 1
13  /
 
[snip]
 
50 rows selected.

The optimizer saw the two group-by’s, and mistakenly decided one could be eliminated. We can work around the problem either by using MATERIALIZE

 
SQL>
SQL> with
  2   inline_view as
  3    ( select /*+ materialize */ cast(ts as int) ts,
  4               count(*) cnt
  5        from   t
  6        group by cast(ts as int)
  7      )
  8  select trunc(ts/100),
  9         sum(cnt) per_min,
10         avg(cnt) avg_cnt_per_min,
11         max(cnt) max_cnt
12  from  inline_view
13  group by trunc(ts/100)
14  order by 1
15  /
 
TRUNC(TS/100)    PER_MIN AVG_CNT_PER_MIN    MAX_CNT
------------- ---------- --------------- ----------
            1     998001        19960.02      20613
 
SQL>

or by setting the initialization parameter “_optimizer_aggr_groupby_elim” to false.

Hope this helps anyone hitting this.

Addenda:

Thanks to Sayan Malakshinov for pointing to some more information on this here: https://jonathanlewis.wordpress.com/2014/09/04/group-by-bug/

Addenda #2:

I’ve just applied the patch to bug 19567916 which claims to fix the issue. The patch was applied on top of 12.1.0.2 bp4, and does not appear to have solved it (at least in my case).