A Look at the Oracle Group-by Bug

Oracle introduced a new feature, group by elimination, for queries where the group by column is also the table's unique key. As with many new features this one still has not had all the kinks resolved. The problem arises when key values are manipulated with function calls. The following example will illustrate the issue by using a table with a DATE as the primary key and by extracting the year is extracted using TO_CHAR or EXTRACT.

Read More: Database Certification

A table is created as follows:

create table bug_test_calendar(
        cal_name   char(17),
        bus_dt   date,
        updt_timestamp       timestamp (6) default systimestamp,
        constraint pk_bug_test_calendar
                        primary key (bus_dt)
)
/

insert into bug_test_calendar (bus_dt)
select
        sysdate + 10 * rownum
from
        all_objects
where
        rownum <= 40
/

commit;

When the query shown below is executed, it produces the following results:

select
        to_char(bus_dt,'YYYY') bus_dt, count(*) ct
from
       bug_test_calendar
group by
        to_char(bus_dt,'YYYY')
order by
        to_char(bus_dt,'YYYY')
/

BUS_DF   CT
-------  --
2020      1
2020      1
...
2020      1

40 rows returned

Oracle doesn't 'know' that the key values have been manipulated so that they are no longer unique, thus the optimizer applies the unique-key-based group-by elimination with less than stellar results,

EXTRACT fares no better, returning the same results. This behavior is controlled by the "_optimizer_aggr_groupby_elim" parameter, which is set to true by default. As it's a hidden parameter, its setting is not reported by Oracle in either of the V$PARAMEter or V$SPPARAMETER views. The work-around is to simply set this parameter to false. However, having it active might help other group-by queries where the unique key values are not manipulated.

Enter Oracle 19c, where this functionality is partially fixed:

select
        to_char(bus_dt,'YYYY') bus_dt, count(*) ct
from
       bug_test_calendar
group by
        to_char(bus_dt,'YYYY')
order by
        to_char(bus_dt,'YYYY')
/

BUS_DF   CT
-------  --
2020     40

Unfortunately EXTRACT is still broken in 19c:

select
        to_char(bus_dt,'YYYY') bus_dt, count(*) ct
from
       bug_test_calendar
group by
        extract(year deom bus_dt)
order by
        extract(year deom bus_dt)
/

BUS_DF   CT
-------  ==
2020      1
2020      1
...
2020      1

40 rows returned

Obviously given truly unique key values a group-by query would produce a count of 1 for each key. And, just as obvious, Oracle should be able to recognize when values are no longer unique and invoke the proper group-by mechanism. It remains to be seen if versions after 19c will fix the second condition and thus return correct results without having to turn off this feature.

This may not affect every installation of Oracle newer than 12.1, but it is worth knowing about should wrong results start appearing in selected group by queries.