Re: Re: fun with years

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

  0          1
  0          1
  0          1
  0          1
  1          1
  1          1
  1          1
  1          1
  1          1
  1          1

        .1
        .2
        .3
        .4
        .5
        .6
        .7
        .8
        .9
         1

> 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