You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bernt M. Johnsen" <be...@sun.com> on 2005/03/03 21:33:21 UTC
DB2 mode vs. Cloudscape mode
Hi all!
In the Derby source, I find several comments referring to "DB2 mode"
and "Cloudscape mode". I can't, however, find any code related to a
distinction between these two modes. Are these comments "leftovers"
from the pre-Derby period of the code, or have I missed something?
The reason I wonder is the error message
ij> create table tab (i integer primary key);
ERROR 42831: 'I' cannot be a column of a primary key or unique key because it can contain null values.
and comments in the code like
> * Set all columns in that appear in a primary/unique key constraint in a create
> * table statement to NOT NULL in Cloudscape mode and raises an error in DB2 mode.
and
> /* For primary/unique/unique keys, verify that the constraint's column
> * list contains valid columns and does not contain any duplicates
> * (Also, all columns in a primary key will be set to non-null,
> but only in Cloudscape mode. SQL and DB2 require explict NOT NULL.
--
Bernt Marius Johnsen, HA Data Management, Sun Microsystems, Norway
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This signature is neither personal, religious, literary,
ethnic, racial, offensive nor humorous.
Re: DB2 mode vs. Cloudscape mode
Posted by Jeremy Boynes <jb...@apache.org>.
Shreyas Kaushik wrote:
>> ij> create table tab (i integer primary key);
>> ERROR 42831: 'I' cannot be a column of a primary key or unique key
>> because it can contain null values.
>>
>>
> This shouldn't happen, NOT NULL should be implicit, right?
>
It should be but isn't - see:
http://issues.apache.org/jira/browse/DERBY-158
--
Jeremy
Re: DB2 mode vs. Cloudscape mode
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
~ Shreyas.
Bernt M. Johnsen wrote:
>Hi all!
>
>In the Derby source, I find several comments referring to "DB2 mode"
>and "Cloudscape mode". I can't, however, find any code related to a
>distinction between these two modes. Are these comments "leftovers"
>from the pre-Derby period of the code, or have I missed something?
>
>The reason I wonder is the error message
>
>ij> create table tab (i integer primary key);
>ERROR 42831: 'I' cannot be a column of a primary key or unique key because it can contain null values.
>
>
This shouldn't happen, NOT NULL should be implicit, right?
>and comments in the code like
>
>
>
>>* Set all columns in that appear in a primary/unique key constraint in a create
>>* table statement to NOT NULL in Cloudscape mode and raises an error in DB2 mode.
>>
>>
>
>and
>
>
>
>> /* For primary/unique/unique keys, verify that the constraint's column
>> * list contains valid columns and does not contain any duplicates
>> * (Also, all columns in a primary key will be set to non-null,
>> but only in Cloudscape mode. SQL and DB2 require explict NOT NULL.
>>
>>
>
>
>
Re: DB2 mode vs. Cloudscape mode - where?
Posted by "Bernt M. Johnsen" <be...@sun.com>.
>>>>>>>>>>>> Jeremy Boynes wrote (2005-03-03 13:16:58):
> Bernt M. Johnsen wrote:
> >Hi all!
> >
> >In the Derby source, I find several comments referring to "DB2 mode"
> >and "Cloudscape mode".
>
> Are there any other examples that you have found?
A quick count gives:
./java/tools/org/apache/derby/impl/tools/dblook/DB_StoredProcedure.java:54: // have to do this cast because DB2 mode doesn't allow equality
./java/engine/org/apache/derby/impl/sql/compile/InsertNode.java:329: * RESOLVE: Enforce the limit only in DB2 mode for now. Too many tests need to be
./java/engine/org/apache/derby/impl/sql/compile/InsertNode.java:330: * migrated to DB2 mode first. Only now we would have expanded STAR (*) to all columns.
./java/engine/org/apache/derby/impl/sql/compile/TableElementList.java:290: but only in Cloudscape mode. SQL and DB2 require explict NOT NULL.
./java/engine/org/apache/derby/impl/sql/compile/TableElementList.java:305: /* Can have only one autoincrement column in DB2 mode */
./java/engine/org/apache/derby/impl/sql/compile/TableElementList.java:898: * table statement to NOT NULL in Cloudscape mode and raises an error in DB2 mode.
./java/engine/org/apache/derby/impl/sql/compile/TableElementList.java:930: * a create table statement to NOT NULL (but only in Cloudscape mode).
./java/engine/org/apache/derby/iapi/types/SQLChar.java:1874: * limits in both modes. We could do these checks in DB2 mode only, if needed, so
./java/engine/org/apache/derby/iapi/types/SQLBinary.java:894: * limits in both modes. We could do these checks in DB2 mode only, if needed, so
./java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java:434: //Need normalize to be called on long varchar for bug 5592 where we need to enforce a lenght limit in db2 mode
> --
> Jeremy
--
Bernt Marius Johnsen, HA Data Management, Sun Microsystems, Norway
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This signature is neither personal, religious, literary,
ethnic, racial, offensive nor humorous.
Re: DB2 mode vs. Cloudscape mode - where?
Posted by Jeremy Boynes <jb...@apache.org>.
Bernt M. Johnsen wrote:
> Hi all!
>
> In the Derby source, I find several comments referring to "DB2 mode"
> and "Cloudscape mode".
Are there any other examples that you have found?
--
Jeremy
Re: DB2 mode vs. Cloudscape mode
Posted by Mamta Satoor <ms...@gmail.com>.
On Thu, 3 Mar 2005 21:33:21 +0100, Bernt M. Johnsen <be...@sun.com> wrote:
> Hi all!
>
> In the Derby source, I find several comments referring to "DB2 mode"
> and "Cloudscape mode". I can't, however, find any code related to a
> distinction between these two modes. Are these comments "leftovers"
> from the pre-Derby period of the code, or have I missed something?
>
> The reason I wonder is the error message
>
> ij> create table tab (i integer primary key);
> ERROR 42831: 'I' cannot be a column of a primary key or unique key because it can contain null values.
>
> and comments in the code like
>
> > * Set all columns in that appear in a primary/unique key constraint in a create
> > * table statement to NOT NULL in Cloudscape mode and raises an error in DB2 mode.
>
> and
>
> > /* For primary/unique/unique keys, verify that the constraint's column
> > * list contains valid columns and does not contain any duplicates
> > * (Also, all columns in a primary key will be set to non-null,
> > but only in Cloudscape mode. SQL and DB2 require explict NOT NULL.
>
> --
> Bernt Marius Johnsen, HA Data Management, Sun Microsystems, Norway
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> NOTICE: This signature is neither personal, religious, literary,
> ethnic, racial, offensive nor humorous.
>
Hi Brent,
Those are left over comments that need to be cleaned up. Derby does
not have 2 modes.
Mamta
Re: DB2 mode vs. Cloudscape mode
Posted by "Bernt M. Johnsen" <be...@sun.com>.
>>>>>>>>>>>> Jeremy Boynes wrote (2005-03-03 13:07:34):
> Also, can anyone think of an issue with making the NOT NULL implicit in
> a PRIMARY KEY specification (per spec but unlike DB2)?
I'll create an issue.
--
Bernt Marius Johnsen, HA Data Management, Sun Microsystems, Norway
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This signature is neither personal, religious, literary,
ethnic, racial, offensive nor humorous.
Re: DB2 mode vs. Cloudscape mode
Posted by RPost <rp...@pacbell.net>.
> "Mike Matrigali" wrote:
> Does anyone know what oracle, microsoft and mysql do?
The Oracle 10g SQL Reference (page 8-12) states:
'To satisfy a unique constraint, no two rows in the table can have the same
value for the unique key. However, the unique key made up of a single column
can contain nulls. To satisfy a composite unique key, no two rows in the
table or view can have the same combination of values in the key columns.
Any row that contains nulls in all key columns automatically satisfies the
constraint. However, two rows that contain nulls for one or more key columns
and the same combination of values for the other key columns violate the
constraint.'
Testing shows this:
1. create table a (col1 number, col2 number);
2. create unique index a_ndx on a (col1, col2);
3. table accepts multiple rows with all null index columns
a. insert into a values (null,null);
b. insert into a values (null,null);
c. insert into a values (null,null);
d. insert into a values (null,null);
4. table does not accept multiple rows with only some null index columns if
non-null columns are duplicates
a. insert into a values (1,null);
b. insert into a values (1,null); - ORA-00001: unique constraint
(SYSTEM.A_NDX) violated
Re: DB2 mode vs. Cloudscape mode
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Satheesh is right, old versions of cloudscape allowed creating unique
indexes on null columns - but only one null was allowed. Currently
derby uses the btree implementation to enforce uniqueness and it has
no support for treating null's differently than other values when
enforcing uniqueness.
I looked at this once and doing that support is not easy, it changes
a lot of basic assumptions in the unique key btree code path. A lot
of the locking and searching optimizations that exist because keys
are known to be unique are no longer valid if one allows multiple nulls.
And it is not as simple as special casing one side of the tree since
a muli-column key can now put duplicates arbitrarily throughout the
tree (ie. 2 column key: (non null value, null value)). The decision
was that the only way to support the standard given the existing
technology was to support a subset
of the standard - it was better to not allow null's in unique indexes
than incorrectly supporting only one null in a unique index.At the time
it was basically a resource
decision, and we did not see customers asking for it very much, it
seemed like a bad design to have null's in your primary key.
Does anyone know what oracle, microsoft and mysql do?
I looked at this once and looked down the road of special casing the
unique index code to support this behavior, but ran into problems. If
I looked at it again I would look at using non-unique indexes if null's
are allowed, and instead special case the insert to check for an
existing matching row if any of the columns are not null. With this
approach I think only insert path code need be changed, all other (this
would
avoid searching problems that I ran into). One could
do this with existing store support by prior to any insert of a row into
the index with any non-null column simply search for the row
giving the key as all of the columns not including the row location at
repeatable read isolation level. It could be done more efficiently
lower in store, at a possible cost of increased code path for all other
btree accesses (but maybe this could be avoided by providing a inherited
implementation for this slightly different kind of btree).
Satheesh Bandaram wrote:
> Yes, Cloudscape used to allow creating UNIQUE indexes on null columns,
> but in a non-standard way. Old cloudscape used to allow only one NULL
> value in the UNIQUE index, where as, I think the standard required
> multiple NULL values.
>
> I think making Derby handle multiple NULLs in UNIQUE index needs changes
> to store/indexing.
>
> Satheesh
>
> Jeremy Boynes wrote:
>
>
>>Satheesh Bandaram wrote:
>>
>>
>>>Yes, you are right... The references to "DB2 mode" is from the past,
>>>leftover comments. There is no distinction between the two.
>>>
>>
>>Did Cloudscape support UNIQUE on possibly null columns? If so, is this
>>a restriction that we can easily remove or is there something deep in
>>the index code that would make this problematic?
>>
>>Also, can anyone think of an issue with making the NOT NULL implicit
>>in a PRIMARY KEY specification (per spec but unlike DB2)?
>>
>>--
>>Jeremy
>>
>>
>>
>
>
>
>
Re: DB2 mode vs. Cloudscape mode
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Please see below for questions.
~ Shreyas
Satheesh Bandaram wrote:
>Yes, Cloudscape used to allow creating UNIQUE indexes on null columns,
>but in a non-standard way. Old cloudscape used to allow only one NULL
>value in the UNIQUE index, where as, I think the standard required
>multiple NULL values.
>
>I think making Derby handle multiple NULLs in UNIQUE index needs changes
>to store/indexing.
>
>
What sort of impact on store? How wide will be the impact on store and
indexing ?
>Satheesh
>
>Jeremy Boynes wrote:
>
>
>
>>Satheesh Bandaram wrote:
>>
>>
>>
>>>Yes, you are right... The references to "DB2 mode" is from the past,
>>>leftover comments. There is no distinction between the two.
>>>
>>>
>>>
>>Did Cloudscape support UNIQUE on possibly null columns? If so, is this
>>a restriction that we can easily remove or is there something deep in
>>the index code that would make this problematic?
>>
>>Also, can anyone think of an issue with making the NOT NULL implicit
>>in a PRIMARY KEY specification (per spec but unlike DB2)?
>>
>>--
>>Jeremy
>>
>>
>>
>>
>>
>
>
>
UNIQUE constraint on posisble null columns (Was: DB2 mode vs. Cloudscape mode)
Posted by "Bernt M. Johnsen" <be...@sun.com>.
>>>>>>>>>>>> Satheesh Bandaram wrote (2005-03-03 16:39:11):
> Yes, Cloudscape used to allow creating UNIQUE indexes on null columns,
> but in a non-standard way. Old cloudscape used to allow only one NULL
> value in the UNIQUE index, where as, I think the standard required
> multiple NULL values.
I think that behaviour was Oracle compliant.
> I think making Derby handle multiple NULLs in UNIQUE index needs changes
> to store/indexing.
Anyway, UNIQUE constraint on possible null columns (SQl-99 Feature
T591) is a non-Core SQL-99 feature.
--
Bernt Marius Johnsen, HA Data Management, Sun Microsystems, Norway
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE: This signature is neither personal, religious, literary,
ethnic, racial, offensive nor humorous.
Re: DB2 mode vs. Cloudscape mode
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Yes, Cloudscape used to allow creating UNIQUE indexes on null columns,
but in a non-standard way. Old cloudscape used to allow only one NULL
value in the UNIQUE index, where as, I think the standard required
multiple NULL values.
I think making Derby handle multiple NULLs in UNIQUE index needs changes
to store/indexing.
Satheesh
Jeremy Boynes wrote:
> Satheesh Bandaram wrote:
>
>> Yes, you are right... The references to "DB2 mode" is from the past,
>> leftover comments. There is no distinction between the two.
>>
>
> Did Cloudscape support UNIQUE on possibly null columns? If so, is this
> a restriction that we can easily remove or is there something deep in
> the index code that would make this problematic?
>
> Also, can anyone think of an issue with making the NOT NULL implicit
> in a PRIMARY KEY specification (per spec but unlike DB2)?
>
> --
> Jeremy
>
>
>
Re: DB2 mode vs. Cloudscape mode
Posted by Jeremy Boynes <jb...@apache.org>.
Satheesh Bandaram wrote:
> Yes, you are right... The references to "DB2 mode" is from the past, leftover
> comments. There is no distinction between the two.
>
Did Cloudscape support UNIQUE on possibly null columns? If so, is this a
restriction that we can easily remove or is there something deep in the
index code that would make this problematic?
Also, can anyone think of an issue with making the NOT NULL implicit in
a PRIMARY KEY specification (per spec but unlike DB2)?
--
Jeremy