I created a table containing date column , i made a query containing group by extract(year from date) ; but when adding a not null constraint the result of the same query was wrong !
is this a bug ?
create table cal1 as
select to_date('20180101','YYYYMMDD') + (level-1) x
from dual connect by to_date('20180101','YYYYMMDD') +(level-1) <= to_date('20181231','YYYYMMDD')
alter table cal1 add constraint cal01_uk unique (x);
the following statement return "12 " which is correct
select count(*)
from
(select
extract(year from x) y,
extract(month from x) m
from cal1
group by extract(year from x) ,extract(month from x)
)
but when define the column as not null the same statement return all the row in the without group
ALTER TABLE cal1 MODIFY (x NOT NULL ENABLE);
select count(*)
from
(select
extract(year from x) y,
extract(month from x) m
from cal1
group by extract(year from x) ,extract(month from x)
)
the statements and the results can be found
- https://livesql.oracle.com/apex/livesql/s/h31qyktger8yg4g5wrjoc5wft