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