Re: strange constraint behavior

Thanks all for reply
Frank, thank for link!

> On 10 Oct 2016, at 17:30, Franck Pachot <franck_at_pachot.net> wrote:
>
> Hi,
> There's a patch for Bug 16791865  "ALTER TABLE .. add .. default '' not null" executes without error <https://support.oracle.com/epmos/faces/DocContentDisplay?id=16791865.8>
> Regards,
> Franck.
>
> On Mon, Oct 10, 2016 at 4:27 PM Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk <mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:
>
> Just tried it in 11.2.0.4 - you're right; and in the second one it's using the constraint to eliminate the predicate and excuting select count(*); "Fixed" in 12c where you get the funny error message about empty tables whether you try to add the column with default '' or default null.
>
>
> Funnier version of the test in 11g:
> SQL> get afiedt.buf
> 1* select count (*) from tt where flag is not null
> SQL> /
>
> COUNT(*)
> ----------
> 100
>
> 1 row selected.
>
> SQL> create index tt_u1 on tt(flag);
>
> Index created.
>
> SQL> get afiedt.buf
> 1* select count (*) from tt where flag is not null
> SQL> /
>
> COUNT(*)
> ----------
> 0
>
> 1 row selected.
>
>
> Create an index and the data vanishes (conversely, drop it and the data re-appears).
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com <http://jonathanlewis.wordpress.com/>
> _at_jloracle
> From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>] on behalf of Toon Koppelaars [toon.koppelaars_at_rulegen.com <mailto:toon.koppelaars_at_rulegen.com>]
> Sent: 10 October 2016 15:13
> To: djeday84_at_gmail.com <mailto:djeday84_at_gmail.com>
> Cc: oracle-l-freelists
> Subject: Re: strange constraint behavior
>
> 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 <mailto: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 <http://www.rulegen.com/>
> TheHelsinkiDeclaration.blogspot.com <http://thehelsinkideclaration.blogspot.com/>
>
> (co)Author: "Applied Mathematics for Database Professionals"
> www.rulegen.com/am4dp-backcover-text <http://www.rulegen.com/am4dp-backcover-text>

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