In your 1st query, very likely the optimizer has injected the constraint
text 'FLAG IS NOT NULL' and through transitive closure it then generated a
"NULL IS NOT NULL" filter predicate.
If you show us the execution plan, we can verify this.
On Mon, Oct 10, 2016 at 3:48 PM, Anton Bushmelev <djeday84@xxxxxxxxx> wrote:
hello, look at new patch from developer and found strange construction,
here I’ll try to reproduce what they want to do:
create table tt as select level id from dual connect by level <=100;
alter table tt add flag varchar2(50) default '' not null ;
Table altered.
select dump (flag,8 ) as dmp from tt ;
DMP
----------
NULL
NULL
…..
NULL
select count (*) from tt where flag is null;
COUNT(*)
----------
0
select count (*) from tt where flag is not null;
COUNT(*)
----------
100
How it is possible ? =)))
ps: if I add default *null, *then all goes well:
alter table tt add flag varchar2(50) default null not null ;
alter table tt add flag varchar2(50) default null not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
create table tt as select level id from dual connect by level <=100;
alter table tt add flag varchar2(50) default null not null ;
alter table tt add flag varchar2(50) default null not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
ps: sorry for my English
