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 "Rick Hillegas (JIRA)" <ji...@apache.org> on 2008/04/17 23:03:21 UTC
[jira] Created: (DERBY-3630) Odd assymetry in interaction of unique
and not null constraints
Odd assymetry in interaction of unique and not null constraints
---------------------------------------------------------------
Key: DERBY-3630
URL: https://issues.apache.org/jira/browse/DERBY-3630
Project: Derby
Issue Type: Improvement
Affects Versions: 10.4.1.3
Reporter: Rick Hillegas
Priority: Minor
If you constrain a column to be both "not null" and "unique", then Derby creates an old-style unique index on the column. If you later remove the "not null" constraint, Derby converts the index to be a new-style unique-with-duplicate-nulls index.
However, if you constrain a column to be "unique" and then later add a "not null" constraint, then Derby does not convert the unique-with-duplicate-nulls index into an old-style unique index.
It seems that the order in which you add these constraints affects the metadata. I don't know if these differences affect the optimizer's decisions.
Here is a script which shows this behavior:
drop table v;
0 rows inserted/updated/deleted
ij> create table v
(
a int,
unique ( a )
);
0 rows inserted/updated/deleted
ij> drop table w;
0 rows inserted/updated/deleted
ij> create table w
(
m int not null,
unique ( m )
);
0 rows inserted/updated/deleted
ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30 )), c.descriptor
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid
and ( t.tablename = 'V' or t.tablename='W')
and c.isconstraint
;
1 |2 |DESCRIPTOR
---------------------------------------------------------
V |SQL080417133332230 |UNIQUE WITH DU&
W |SQL080417133332330 |UNIQUE BTREE (&
2 rows selected
ij> alter table v
alter column a not null
;
0 rows inserted/updated/deleted
ij> alter table w
alter column m null
;
0 rows inserted/updated/deleted
ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30 )), c.descriptor
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid
and ( t.tablename = 'V' or t.tablename='W')
and c.isconstraint
;
1 |2 |DESCRIPTOR
---------------------------------------------------------
V |SQL080417133332230 |UNIQUE WITH DU&
W |SQL080417133332330 |UNIQUE WITH DU&
2 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3630) Odd assymetry in interaction of unique
and not null constraints
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-3630:
----------------------------------
I think it would be better if in any case when it is known that all columns are not-null that old style index is used.
This index will always either perform the same or better than the new index in cases where only non-null data is present.
Basically these two factors allow the index to not execute extra special case checking code. Any constrained column
that is nullable must be the new style index.
You may want to add to your test case to make sure that resulting index actually does what is expected both the expected error case for existing rows in the table and the subsequent expected
error case for inserting subsequent rows. In the above case W should fail if there are any null's already in table, and
should fail if trying to insert any null's. It may work, I just know the intent of the final code was the new index was just
going to handle the unique, null case.
> Odd assymetry in interaction of unique and not null constraints
> ---------------------------------------------------------------
>
> Key: DERBY-3630
> URL: https://issues.apache.org/jira/browse/DERBY-3630
> Project: Derby
> Issue Type: Improvement
> Affects Versions: 10.4.1.3
> Reporter: Rick Hillegas
> Priority: Minor
>
> If you constrain a column to be both "not null" and "unique", then Derby creates an old-style unique index on the column. If you later remove the "not null" constraint, Derby converts the index to be a new-style unique-with-duplicate-nulls index.
> However, if you constrain a column to be "unique" and then later add a "not null" constraint, then Derby does not convert the unique-with-duplicate-nulls index into an old-style unique index.
> It seems that the order in which you add these constraints affects the metadata. I don't know if these differences affect the optimizer's decisions.
> Here is a script which shows this behavior:
> drop table v;
> 0 rows inserted/updated/deleted
> ij> create table v
> (
> a int,
> unique ( a )
> );
> 0 rows inserted/updated/deleted
> ij> drop table w;
> 0 rows inserted/updated/deleted
> ij> create table w
> (
> m int not null,
> unique ( m )
> );
> 0 rows inserted/updated/deleted
> ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30 )), c.descriptor
> from sys.sysconglomerates c, sys.systables t
> where c.tableid = t.tableid
> and ( t.tablename = 'V' or t.tablename='W')
> and c.isconstraint
> ;
> 1 |2 |DESCRIPTOR
> ---------------------------------------------------------
> V |SQL080417133332230 |UNIQUE WITH DU&
> W |SQL080417133332330 |UNIQUE BTREE (&
> 2 rows selected
> ij> alter table v
> alter column a not null
> ;
> 0 rows inserted/updated/deleted
> ij> alter table w
> alter column m null
> ;
> 0 rows inserted/updated/deleted
> ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30 )), c.descriptor
> from sys.sysconglomerates c, sys.systables t
> where c.tableid = t.tableid
> and ( t.tablename = 'V' or t.tablename='W')
> and c.isconstraint
> ;
> 1 |2 |DESCRIPTOR
> ---------------------------------------------------------
> V |SQL080417133332230 |UNIQUE WITH DU&
> W |SQL080417133332330 |UNIQUE WITH DU&
> 2 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3630) Odd assymetry in interaction of unique
and not null constraints
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3630:
---------------------------------
Component/s: SQL
> Odd assymetry in interaction of unique and not null constraints
> ---------------------------------------------------------------
>
> Key: DERBY-3630
> URL: https://issues.apache.org/jira/browse/DERBY-3630
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.4.1.3
> Reporter: Rick Hillegas
> Priority: Minor
>
> If you constrain a column to be both "not null" and "unique", then Derby creates an old-style unique index on the column. If you later remove the "not null" constraint, Derby converts the index to be a new-style unique-with-duplicate-nulls index.
> However, if you constrain a column to be "unique" and then later add a "not null" constraint, then Derby does not convert the unique-with-duplicate-nulls index into an old-style unique index.
> It seems that the order in which you add these constraints affects the metadata. I don't know if these differences affect the optimizer's decisions.
> Here is a script which shows this behavior:
> drop table v;
> 0 rows inserted/updated/deleted
> ij> create table v
> (
> a int,
> unique ( a )
> );
> 0 rows inserted/updated/deleted
> ij> drop table w;
> 0 rows inserted/updated/deleted
> ij> create table w
> (
> m int not null,
> unique ( m )
> );
> 0 rows inserted/updated/deleted
> ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30 )), c.descriptor
> from sys.sysconglomerates c, sys.systables t
> where c.tableid = t.tableid
> and ( t.tablename = 'V' or t.tablename='W')
> and c.isconstraint
> ;
> 1 |2 |DESCRIPTOR
> ---------------------------------------------------------
> V |SQL080417133332230 |UNIQUE WITH DU&
> W |SQL080417133332330 |UNIQUE BTREE (&
> 2 rows selected
> ij> alter table v
> alter column a not null
> ;
> 0 rows inserted/updated/deleted
> ij> alter table w
> alter column m null
> ;
> 0 rows inserted/updated/deleted
> ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30 )), c.descriptor
> from sys.sysconglomerates c, sys.systables t
> where c.tableid = t.tableid
> and ( t.tablename = 'V' or t.tablename='W')
> and c.isconstraint
> ;
> 1 |2 |DESCRIPTOR
> ---------------------------------------------------------
> V |SQL080417133332230 |UNIQUE WITH DU&
> W |SQL080417133332330 |UNIQUE WITH DU&
> 2 rows selected
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.