dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted April 13, 2020

A Look at the Oracle Group-by Bug

By David Fitzjarrell

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.

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.

# # #

See articles by David Fitzjarrell



Oracle Archives




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM