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@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
