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@xxxxxxxxxxxxxxxxxx>
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
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Toon Koppelaars [toon.koppelaars@xxxxxxxxxxx]
*Sent:* 10 October 2016 15:13
*To:* djeday84@xxxxxxxxx
*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@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
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com
(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text
