MIME-Version: 1.0
In-Reply-To: <4812A130-901F-429D-AA1D-4CDB9DB20CB4@gmail.com>
References: <4812A130-901F-429D-AA1D-4CDB9DB20CB4@gmail.com>
From: Toon Koppelaars <toon.koppelaars@rulegen.com>
Date: Mon, 10 Oct 2016 16:13:32 +0200
Message-ID: <CAA9w=EvH1x3dv-wuqb5YtHdUv9Z+7YQboez=W4Bt2v32V-yyyw@mail.gmail.com>
Subject: Re: strange constraint behavior
To: djeday84@gmail.com
Cc: oracle-l-freelists <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary=001a1145e1ca713172053e835da9
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: toon.koppelaars@rulegen.com
--001a1145e1ca713172053e835da9
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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@gmail.com> wrote=
:

> hello, look at new patch from developer and found strange construction,
> here I=E2=80=99ll try to reproduce what they want to do:
>
> create table tt as select level id from dual connect by level <=3D100;
> alter table tt add flag varchar2(50)  default '' not null ;
> Table altered.
>
> select dump (flag,8 ) as dmp from tt ;
>
> DMP
> ----------
> NULL
> NULL
> =E2=80=A6..
> 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 ? =3D)))
>
>
> 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 <=3D100;
>  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
>
>


--=20
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text

--001a1145e1ca713172053e835da9
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">In your 1st query, very likely the optimizer has injected =
the constraint text &#39;FLAG IS NOT NULL&#39; and through transitive closu=
re it then generated a &quot;NULL IS NOT NULL&quot; filter predicate.<div><=
br></div><div>If you show us the execution plan, we can verify this.</div><=
div><br></div></div><div class=3D"gmail_extra"><br><div class=3D"gmail_quot=
e">On Mon, Oct 10, 2016 at 3:48 PM, Anton Bushmelev <span dir=3D"ltr">&lt;<=
a href=3D"mailto:djeday84@gmail.com" target=3D"_blank">djeday84@gmail.com</=
a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0=
 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style=3D"word-w=
rap:break-word"><div>hello, look at new patch from developer and found stra=
nge construction, here I=E2=80=99ll try to reproduce what they want to do:<=
/div><div><br></div><div>create table tt as select level id from dual conne=
ct by level &lt;=3D100;=C2=A0</div><div>alter table tt add flag varchar2(50=
) =C2=A0default &#39;&#39; not null ;</div><div>Table altered.</div><div><b=
r></div><div>select dump (flag,8 ) as dmp from tt ;</div><div><br></div><di=
v>DMP</div><div>----------</div><div>NULL</div><div>NULL</div><div>=E2=80=
=A6..</div><div>NULL</div><div><br></div><div><br></div><div>=C2=A0select c=
ount (*) from tt where flag is null;</div><div><br></div><div>=C2=A0 COUNT(=
*)</div><div>----------</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00</div>=
<div><br></div><div>select count (*) from tt where flag is not null;</div><=
div><br></div><div>=C2=A0 COUNT(*)</div><div>----------</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0100</div><div><br></div><div><br></div><div>How it is p=
ossible ? =3D)))</div><div><br></div><div><br></div><div>ps: if I add defau=
lt <b>null, </b>then all goes well:</div><div>=C2=A0</div><div>=C2=A0alter =
table tt add flag varchar2(50) =C2=A0default null not null ;</div><div>alte=
r table tt add flag varchar2(50) =C2=A0default null not null</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 *</div><div>ERROR at line 1:</div><d=
iv>ORA-01758: table must be empty to add mandatory (NOT NULL) column=C2=A0<=
/div><div><div>=C2=A0</div><div>create table tt as select level id from dua=
l connect by level &lt;=3D100;</div><div>=C2=A0alter table tt add flag varc=
har2(50) =C2=A0default null not null ;</div><div>alter table tt add flag va=
rchar2(50) =C2=A0default null not null</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 *</div><div>ERROR at line 1:</div><div>ORA-01758: table m=
ust be empty to add mandatory (NOT NULL) column</div></div><div><br></div><=
div><br></div><div>ps: sorry for my English</div><div>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0</div></div></blockquote></div><br><br clear=3D"all"><div><br></div>-=
- <br><div class=3D"gmail_signature" data-smartmail=3D"gmail_signature">Too=
n Koppelaars<br>RuleGen BV<br>Toon.Koppelaars@RuleGen.com<br><a href=3D"htt=
p://www.RuleGen.com" target=3D"_blank">www.RuleGen.com</a><br><a href=3D"ht=
tp://TheHelsinkiDeclaration.blogspot.com" target=3D"_blank">TheHelsinkiDecl=
aration.blogspot.com</a><br><br>(co)Author: &quot;Applied Mathematics for D=
atabase Professionals&quot;<br><a href=3D"http://www.rulegen.com/am4dp-back=
cover-text" target=3D"_blank">www.rulegen.com/am4dp-backcover-text</a><br><=
/div>
</div>

--001a1145e1ca713172053e835da9--
--
http://www.freelists.org/webpage/oracle-l