Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 8681B19615E7 for ; Mon, 10 Oct 2016 16:32:04 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Mon, 10 Oct 2016 16:32:04 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 80D82306E2; Mon, 10 Oct 2016 10:32:03 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id J64324e2EFQw; Mon, 10 Oct 2016 10:32:03 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5856E2FF32; Mon, 10 Oct 2016 10:31:50 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 10 Oct 2016 10:30:28 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 55DA52F66C for ; Mon, 10 Oct 2016 10:30:28 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id itxCrvcNsQ3e for ; Mon, 10 Oct 2016 10:30:28 -0400 (EDT) Received: from relay2-d.mail.gandi.net (relay2-d.mail.gandi.net [217.70.183.194]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id B13322F5DD for ; Mon, 10 Oct 2016 10:30:25 -0400 (EDT) Received: from mfilter28-d.gandi.net (mfilter28-d.gandi.net [217.70.178.159]) by relay2-d.mail.gandi.net (Postfix) with ESMTP id 1BA77C5AA1 for ; Mon, 10 Oct 2016 16:30:24 +0200 (CEST) Received: from relay2-d.mail.gandi.net ([IPv6:::ffff:217.70.183.194]) by mfilter28-d.gandi.net (mfilter28-d.gandi.net [::ffff:10.0.15.180]) (amavisd-new, port 10024) with ESMTP id LFTPzD4ecZ8n for ; Mon, 10 Oct 2016 16:30:22 +0200 (CEST) X-Originating-IP: 209.85.214.42 Received: from mail-it0-f42.google.com (mail-it0-f42.google.com [209.85.214.42]) (Authenticated sender: mailbox@pachot.net) by relay2-d.mail.gandi.net (Postfix) with ESMTPSA id B70D3C5A79 for ; Mon, 10 Oct 2016 16:30:21 +0200 (CEST) Received: by mail-it0-f42.google.com with SMTP id e203so14827719itc.0 for ; Mon, 10 Oct 2016 07:30:21 -0700 (PDT) X-Gm-Message-State: AA6/9Rk/Ku3jhv3YopYmicBi2EqFe2jyk3A+MAvzJVzy3ihkr2vTyO/dA/ywMyPx5bVD+DeOwNtzUIDpxOTZew== X-Received: by 10.36.155.84 with SMTP id o81mr11703619itd.90.1476109820212; Mon, 10 Oct 2016 07:30:20 -0700 (PDT) MIME-Version: 1.0 References: <4812A130-901F-429D-AA1D-4CDB9DB20CB4@gmail.com> In-Reply-To: From: Franck Pachot Date: Mon, 10 Oct 2016 14:30:09 +0000 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: strange constraint behavior To: jonathan@jlcomp.demon.co.uk, oracle-l-freelists Content-Type: multipart/alternative; boundary=94eb2c06006c7bfa91053e8399dc X-archive-position: 66442 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: franck@pachot.net Precedence: normal Reply-To: franck@pachot.net List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --94eb2c06006c7bfa91053e8399dc Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, There's a patch for Bug 16791865 "ALTER TABLE .. add .. default '' not null" executes without error Regards, Franck. On Mon, Oct 10, 2016 at 4:27 PM Jonathan Lewis wrote: > > Just tried it in 11.2.0.4 - you're right; and in the second one it's usin= g > 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@freelists.org [oracle-l-bounce@freelists.org] on > behalf of Toon Koppelaars [toon.koppelaars@rulegen.com] > *Sent:* 10 October 2016 15:13 > *To:* djeday84@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 > 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 > > > > > > -- > 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 > --94eb2c06006c7bfa91053e8399dc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
<= div class=3D"gmail_quote">
On Mon, Oct 10, 2016 at 4:27 PM = Jonathan Lewis <jonathan@= 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= (*);=C2=A0 "Fixed" in 12c where you get the funny error message a= bout 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
=C2=A0 1* select count (*) from tt where flag is not null
SQL> /

=C2=A0 COUNT(*)
----------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 100

1 row selected.

SQL> create index tt_u1 on tt(flag);

Index created.

SQL> get afiedt.buf
=C2=A0 1* select count (*) from tt where flag is not null
SQL> /

=C2=A0 COUNT(*)
----------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 0

1 row selected.


Create an index and the data vanishes (conversely, drop it and the data re-= appears).



=C2=A0=C2=A0
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
From: oracle-l-bounce@fr= eelists.org [oracle-l-bounce@freelists.org] on behalf o= f Toon Koppelaars [toon.koppelaars@rulegen.com]
Sent: 10 October 2016 15:13
To: djeday84@gmail.com
Cc: oracle-l-freelists
Subject: Re: strange constraint behavior

In your 1st query, very likely the opt= imizer has injected the constraint text 'FLAG IS NOT NULL' and thro= ugh transitive closure it then generated a "NULL IS NOT NULL" fil= ter predicate.

If you show us the execution plan, we can verify t= his.


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 conne= ct by level <=3D100;=C2=A0
alter table tt add flag varchar2(50) =C2=A0default= '' not null ;
Table altered.

select dump (flag,8 ) as dmp from tt ;

DMP
----------
NULL
NULL
=E2=80=A6..
NULL


=C2=A0select count (*) from tt where flag is null;=

=C2=A0 COUNT(*)
----------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A00

select count (*) from tt where flag is not null;

=C2=A0 COUNT(*)
----------
=C2=A0 =C2=A0 =C2=A0 =C2=A0100


How it is possible ? =3D)))


ps: if I add default null, = then all goes well:
=C2=A0
=C2=A0alter table tt add flag varchar2(50) =C2=A0d= efault null not null ;
alter table tt add flag varchar2(50) =C2=A0default= null not null
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (N= OT NULL) column=C2=A0
=C2=A0
create table tt as select level id from dual conne= ct by level <=3D100;
=C2=A0alter table tt add flag varchar2(50) =C2=A0d= efault null not null ;
alter table tt add flag varchar2(50) =C2=A0default= null not null
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (N= OT NULL) column


ps: sorry for my English
=C2=A0 =C2=A0 =C2=A0 =C2=A0



--
Toon Koppela= ars
RuleGen BV
Toon.Koppelaars@RuleGen.com
ww= w.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text
--94eb2c06006c7bfa91053e8399dc-- -- http://www.freelists.org/webpage/oracle-l