Oracle Scratchpad

September 4, 2014

Group By Bug

Filed under: 12c,Bugs,dbms_xplan,Execution plans,Oracle — Jonathan Lewis @ 5:11 pm BST Sep 4,2014

This just in from OTN Database Forum – a surprising little bug with “group by elimination” exclusive to 12c.


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_hints only in the 12.1.0.2.

6 Comments »

  1. 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 | Reply

    • 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 | Reply

  2. 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 | Reply

    • 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 | Reply

  3. […] 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 BST Feb 12,2015 | Reply

  4. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.