db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Anurag Shekhar (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-2212) Add "Unique where not null" to create index
Date Tue, 16 Oct 2007 11:26:51 GMT

     [ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Anurag Shekhar updated DERBY-2212:
----------------------------------

    Attachment: derby-2212preview2.diff

Sorry for the delay in posting patch. It took me lot longer than expected to fix 
drop table issue.

Major change in this patch is additional attribute in B2I. This attribute is to 
announce whether for this particular index should nulls should be treated 
equal or not. This attributed is persisted while storing the index. This attribute 
is required to insure the data dictionary index retains the old behavior. 

Data dictionary classes use ControlRow class to locate index while deleting 
the table, in my previous patch drop table was failing because ControlRow was 
treating nulls unequal, unconditionally. In this patch I am passing additional 
attribute to ControlRow to tell if nulls should be equal or not. The information 
whether nulls should be treat equal or not is fetched from BTree class.



Description of the patch
java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java

Modified to make IS NULL predicate as optimizeable as IS NULL on indexed 
field doesn't ensures single record in result.

java/engine/org/apache/derby/impl/sql/execute/CreateIndexConstantAction.java

modified to set nulls are not equal in sorter. This routine is executed only while 
user is creating the index, so it doesn't effects internal data dictionary indexes.

java/engine/org/apache/derby/impl/store/access/sort/SortBuffer.java
java/engine/org/apache/derby/impl/store/access/sort/MergeSort.java
java/engine/org/apache/derby/impl/store/access/sort/MergeInserter.java
java/engine/org/apache/derby/impl/store/access/heap/HeapScan.java
java/engine/org/apache/derby/iapi/store/access/SortController.java

Modified to add additional functionality to handle unequal nulls.

java/engine/org/apache/derby/impl/store/access/btree/BTree.java
Added two abstract methods to set and get how nulls should be treated (equal 
or unequal)


java/engine/org/apache/derby/impl/store/access/btree/BTreeController.java

Modified to call areNullsEqual() on Btree and pass it on to ControlRow.

java/engine/org/apache/derby/impl/store/access/btree/BTreeScan.java

added addional method to modify the the behaviour of null treatment and to 
retrieve the behaviour.

java/engine/org/apache/derby/impl/store/access/btree/ControlRow.java

added new method to accept flag about how null should be treated and further 
pass it on to DataValueDescriptor.

java/engine/org/apache/derby/impl/store/access/btree/index/B2I.java

added additional attribute to indicate whether this index treats nulls equal or nor.
added code to store and retrieve this flag in secondary storage.
added code to retrieve this property while creation.



java/engine/org/apache/derby/iapi/types/DataValueDescriptor.java
java/engine/org/apache/derby/iapi/types/DataType.java

added additional method to specify how null should be compared.


I have also fixed spelling and while space issues Øystein.


> Add "Unique where not null" to create index
> -------------------------------------------
>
>                 Key: DERBY-2212
>                 URL: https://issues.apache.org/jira/browse/DERBY-2212
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.2.1.6
>            Reporter: Oleksandr Alesinskyy
>            Assignee: Anurag Shekhar
>         Attachments: derby-2212preview.diff, derby-2212preview2.diff
>
>
> Derby prohibits creation of unique constraints on nullable colums (as well if only some
columns in the constraint list are nullable) and treat nulls in unique indexes as normal values
(i.e. only one row with null values in indexed columns may be inserted into the table). This
bahavior is very restrictive, does not completely comply with SQL standards (both letter and
intent) as well as with business needs and intending meaning of NULL values (2 null values
are not considered as equal, this comparision shall return NULL, and for selection criteria
boolean null is treated as FALSE).
> This behavior, as far as I can see, is modelled after DB2 (and differs from behavior
of most other major databases, like SyBase, Oracle, etc.).
> But even DB2 provide some means to alleviate these restrictions, namely "UNIQUE WHERE
NOT NULL" clause for CREATE INDEX statement.
> It will be very good if such "UNIQUE WHERE NOT NULL" clause will be introduced in Derby.
> Regards,
> Oleksandr Alesinskyy

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