Re: strange constraint behavior
Date: Mon, 10 Oct 2016 16:13:32 +0200
Message-ID: <CAA9w=EvH1x3dv-wuqb5YtHdUv9Z+7YQboez=W4Bt2v32V-yyyw_at_mail.gmail.com>
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_at_gmail.com> 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
>
>
-- Toon Koppelaars RuleGen BV Toon.Koppelaars_at_RuleGen.com www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.rulegen.com/am4dp-backcover-text -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 10 2016 - 16:13:32 CEST