This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12.1.0.2.
rem
rem Script: 12c_group_by_bug.sql
rem Author: Jonathan Lewis
rem Dated: Sept 2014
rem
alter session set nls_date_format='dd-Mon-yyyy hh24:mi:ss';
select
/* optimizer_features_enable('12.1.0.1')*/
trunc (ts,'DD') ts1, sum(fieldb) fieldb
from (
select
ts, max(fieldb) fieldb
from (
select trunc(sysdate) - 1/24 ts, 1 fieldb from dual
union all
select trunc(sysdate) - 2/24 ts, 2 fieldb from dual
union all
select trunc(sysdate) - 3/24 ts, 3 fieldb from dual
union all
select trunc(sysdate) - 4/24 ts, 4 fieldb from dual
union all
select trunc(sysdate) - 5/24 ts, 5 fieldb from dual
)
group by ts
)
group by trunc (ts,'DD')
/
You might expect to get one row as the answer – but this is the result I got, with the execution plan pulled from memory:
TS1 FIELDB -------------------- ---------- 03-Sep-2014 00:00:00 1 03-Sep-2014 00:00:00 5 03-Sep-2014 00:00:00 4 03-Sep-2014 00:00:00 2 03-Sep-2014 00:00:00 3 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 11 (100)| | | 1 | HASH GROUP BY | | 5 | 60 | 11 (10)| 00:00:01 | | 2 | VIEW | | 5 | 60 | 10 (0)| 00:00:01 | | 3 | UNION-ALL | | | | | | | 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------
You’ll notice that I’ve got an “optimizer_features_enable()” comment in the code: if I change it into a hint I get the following (correct) result and plan:
TS1 FIELDB -------------------- ---------- 03-Sep-2014 00:00:00 15 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 12 (100)| | | 1 | HASH GROUP BY | | 5 | 60 | 12 (17)| 00:00:01 | | 2 | VIEW | | 5 | 60 | 11 (10)| 00:00:01 | | 3 | HASH GROUP BY | | 5 | 60 | 11 (10)| 00:00:01 | | 4 | VIEW | | 5 | 60 | 10 (0)| 00:00:01 | | 5 | UNION-ALL | | | | | | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------
Somehow 12.1.0.2 has managed to get confused by the combination of “group by ts” and “group by trunc(ts,’DD’)” and has performed “group-by elimination” when it shouldn’t have. If you use the ‘outline’ option for dbms_xplan.display_cursor() you’ll find that the bad result reports the hint elim_groupby(@sel$1), which leads to an alternative solution to hinting the optimizer_features level. Start the code like this:
select
/*+ qb_name(main) no_elim_groupby(@main) */
trunc (ts,'DD') ts1, sum(fieldb) fieldb
from ...
The (no_)elim_groupby is a hint that appeared in v$sql_hint only in the 12.1.0.2.
Oracle Scratchpad 
Hi Jonathan,
I think the “trunc(sysdate) – 1/24 ts” should read “trunc(sysdate – 1/24) ts”, because otherwise your sentence with “Provided you aren’t running this some time between midnight and 5:00 a.m.” does not make sense.
For 11.2, I get the correct plan (as expected).
Best regards,
Salek Talangi
Comment by Salek Talangi — September 4, 2014 @ 7:15 pm BST Sep 4,2014 |
Salek,
It’s silly, but I added that “midnight to 5:00 am” bit moments before publishing. It just occurred to me at the last moment that someone would say something about a 5 hour gap and I didn’t think it through properly. I’ve now deleted the error.
Comment by Jonathan Lewis — September 4, 2014 @ 7:30 pm BST Sep 4,2014 |
The feature can be turned off with a hidden parameter too if the problem creeps into too many queries to be hintable.
alter session set “_optimizer_aggr_groupby_elim” = false;
Comment by Sean D. Stuber — September 4, 2014 @ 9:28 pm BST Sep 4,2014 |
Sean,
Thanks for the comment; although I scanned the fix_control I forgot to check the list of optimizer parameters.
Available only in 12.1.0.2 – and worth checking whether it would be effective in the opt_param(), of course: so I did, and it is /*+ opt_param(‘_optimizer_aggr_groupby_elim’ ‘false’) */
Comment by Jonathan Lewis — September 5, 2014 @ 9:31 am BST Sep 5,2014 |
[…] Thanks to Sayan Malakshinov for pointing to some more information on this here: https://jonathanlewis.wordpress.com/2014/09/04/group-by-bug/ […]
Pingback by GROUP BY – wrong results in 12.1.0.2 | Learning is not a spectator sport — February 12, 2015 @ 2:26 pm GMT Feb 12,2015 |
This just in via Brian FitzGerald (@ExaGridDba) on Twitter:
“12.1.0.2 patch 19567916 actually does fix the infamous gby elimination bug.”
With a follow-up from Mike Dietrich (@MikeDietrichDE):
“True – but there are actually one or two other “wrong query results” bugs with this functionality :-(“
Comment by Jonathan Lewis — April 2, 2015 @ 10:12 am BST Apr 2,2015 |