Thanks Tim !
Interestingly, the Note only mentions "extract".
But extraction using "to_char" behaves exactly the same:
sql > select extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD) COUNT(*)
------------------ ----------
2021 1
2021 1
sql > select to_char(d, 'yyyy'), count(*) from t group by to_char(d, 'yyyy');
TO_C COUNT(*)
---- ----------
2021 1
2021 1
sql > ALTER SESSION SET "_fix_control" = '23210039:0';
Session wurde geõndert.
sql > select extract(year from d), count(*) from t group by extract(year from d);
EXTRACT(YEARFROMD) COUNT(*)
------------------ ----------
2021 2
sql > select to_char(d, 'yyyy'), count(*) from t group by to_char(d, 'yyyy');
TO_C COUNT(*)
---- ----------
2021 2
Gesendet: Donnerstag, 30. Dezember 2021 um 20:16 Uhr
Von: "Tim Gorman" <tim.evdbt@xxxxxxxxx>
An: rogel@xxxxxx, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Betreff: Re: fun with years
Oracle Support Document 2629968.1 (Wrong Result Using GROUP BY with EXTRACT Function Against DATE)
From the Solution section in the note...
The bug is still being worked on. Please use one of the workarounds:
ALTER SESSION SET optimizer_features_enable = '12.1.0.2';
or
ALTER SESSION SET "_fix_control" = '23210039:0';
or
ALTER SESSION SET "_optimizer_aggr_groupby_elim" = FALSE;
Dear listers, is this a known bug (version is 12.2.0.1) ? SQL> create table t as (select date'2021-12-30' d from dual union all select date'2021-12-31' from dual); Tabelle wurde erstellt. SQL> select extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD) COUNT(*)-- //www.freelists.org/webpage/oracle-l
------------------ ----------
2021 2 SQL> alter table t add primary key(d); Tabelle wurde geõndert. SQL> select extract(year from d), count(*) from t group by extract(year from d); EXTRACT(YEARFROMD) COUNT(*)
------------------ ----------
2021 1
2021 1
Thanks and a Happy New Year ! Matthias -- //www.freelists.org/webpage/oracle-l
