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.
0 comments:
Post a Comment