Skip to Main Content
  • Questions

  • Nested grouping fails to return unique values

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Stefan.

Asked: January 17, 2018 - 3:02 pm UTC

Last updated: January 17, 2018 - 3:50 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Tom
I'm using the following environment (which I believe is relevant because a similar query broke after migrating from 11 to 12c):

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production                                      
CORE 12.1.0.2.0 Production                                                  
TNS for Linux: Version 12.1.0.2.0 - Production                              
NLSRTL Version 12.1.0.2.0 - Production


The following query

select trunc(SEC), SUM(SEC_COUNT) 
from (
       select CREATED as SEC, COUNT(* ) as SEC_COUNT 
       from ALL_OBJECTS group by CREATED
     ) 
group by trunc(SEC) 
order by 1 asc
;

returns this result

TRUNC(SECOND)   SUM(SECOND_COUNT)
8-Feb-2012 9 
8-Feb-2012 1 
8-Feb-2012 55
8-Feb-2012 64
8-Feb-2012 58
...


The grouping column shows many duplicate values where I expect only one (grouping per per day).
The real query also deals with max counts per second which disallows rewriting with a single aggregation granularity.

Thanks for pointing me to the misconception I must have on this.

Regards,
Stefan

and Chris said...

I see the same:

select trunc( sec ) dt,
       sum( sec_count )
from (
  select created as sec,
         count(*) as sec_count
  from   all_objects
  group by created
)
group by trunc( sec )
order by 1;

DT                     SUM(SEC_COUNT)   
07-JUL-2014 00:00:00                 70 
07-JUL-2014 00:00:00                 30 
07-JUL-2014 00:00:00                 80 
07-JUL-2014 00:00:00                 87 
07-JUL-2014 00:00:00                 64 
07-JUL-2014 00:00:00                 49 
07-JUL-2014 00:00:00                 50 
07-JUL-2014 00:00:00                 74 
07-JUL-2014 00:00:00                 95 
07-JUL-2014 00:00:00                 56 
07-JUL-2014 00:00:00                 40 
07-JUL-2014 00:00:00                 77 
07-JUL-2014 00:00:00                 58 
...


This looks like it's related to Bug 26588069 (currently only reported for 12.2). Contact support if you need a patch.

In the meantime, the following workaround suggested in MOS note 26588069.8 does the trick:

ALTER SESSION SET "_optimizer_aggr_groupby_elim" = FALSE;

select trunc( sec ) dt,
       sum( sec_count )
from (
  select created as sec,
         count(*) as sec_count
  from   all_objects
  group by created
)
group by trunc( sec )
order by 1;

DT                     SUM(SEC_COUNT)   
07-JUL-2014 00:00:00              89359 
08-AUG-2016 00:00:00                  4 
09-AUG-2016 00:00:00                 55 
10-AUG-2016 00:00:00                  2 
11-AUG-2016 00:00:00                  4 
12-AUG-2016 00:00:00                  2 
15-AUG-2016 00:00:00                  3 
17-AUG-2016 00:00:00                  1 
18-AUG-2016 00:00:00                  2
...


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.