db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3630) Odd assymetry in interaction of unique and not null constraints
Date Thu, 17 Apr 2008 22:53:21 GMT

     [ 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.


Mime
View raw message