And it's not just dates.
SQL> select to_char(n1,'99') , count(*) from t3 group by to_char(n1,'99');
TO_ COUNT(*)
--- ----------
1 6
0 4
2 rows selected.
SQL> alter table t3 add constraint t3_pk primary key(n1);
Table altered.
SQL> select to_char(n1,'99') , count(*) from t3 group by to_char(n1,'99');
TO_ COUNT(*)
--- ----------
0 1
0 1
0 1
0 1
1 1
1 1
1 1
1 1
1 1
1 1
10 rows selected.
SQL> select n1 from t3;
N1
----------
.1
.2
.3
.4
.5
.6
.7
.8
.9
1
10 rows selected.
Regards
Jonathan Lewis
On Thu, 30 Dec 2021 at 19:50, <rogel@xxxxxx> wrote:
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
<https://support.oracle.com/epmos/faces/DocumentDisplay?id=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@xxxxxx wrote:
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(*) ------------------ ----------
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
-- //www.freelists.org/webpage/oracle-l
