Re: fun with years

Looks like a bug.
It doesn't reproduce in 19.11.

Add /*+ no_elim_groupby */ hint to the query and the problem goes away. I may have seen this before - I think Oracle sees the PK on (d) and "thinks" that it still applies to extract(year from d) , therefore thinks every row has a unique year.
If you check the execution plan you'll see only an index full scan and no aggregation operation.

Regards
Jonathan Lewis

On Thu, 30 Dec 2021 at 18:56, <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