Aw: Re: fun with years
From: <rogel_at_web.de>
Date: Thu, 30 Dec 2021 20:50:16 +0100
Message-ID: <trinity-b9aec55e-b481-42df-b12a-1263beab05c3-1640893816282_at_3c-app-webde-bap41>
Oracle Support Document 2629968.1 (Wrong Result Using GROUP BY with EXTRACT Function Against DATE)
From the Solution section in the note...
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 30 2021 - 20:50:16 CET
Date: Thu, 30 Dec 2021 20:50:16 +0100
Message-ID: <trinity-b9aec55e-b481-42df-b12a-1263beab05c3-1640893816282_at_3c-app-webde-bap41>
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
------------------ ----------
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
---- ----------
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
------------------ ----------
2021 2
sql > select to_char(d, 'yyyy'), count(*) from t group by to_char(d, 'yyyy');
TO_C COUNT(*)
---- ----------
2021 2
---- ----------
2021 2
Gesendet: Donnerstag, 30. Dezember 2021 um 20:16 Uhr
Von: "Tim Gorman" <tim.evdbt_at_gmail.com>
An: rogel_at_web.de, "oracle-l" <oracle-l_at_freelists.org>
Betreff: Re: fun with years
Von: "Tim Gorman" <tim.evdbt_at_gmail.com>
An: rogel_at_web.de, "oracle-l" <oracle-l_at_freelists.org>
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;
On 12/30/2021 10:56 AM, rogel_at_web.de wrote:
-- http://www.freelists.org/webpage/oracle-lDear 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(*)
------------------ ----------
2021 2SQL> 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