Re: Re: fun with years

It is fixed in bug no 26588069 (check doc 26588069.8).

On Thu, Dec 30, 2021 at 10:04 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> 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_at_web.de> 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_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
>> <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_at_web.de 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
>> -- http://www.freelists.org/webpage/oracle-l
>>
>> -- http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l