[
https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Anurag Shekhar updated DERBY-2212:
----------------------------------
Attachment: derby-2212preview.diff
This patch is not meant for commit.
Its just for review of the approach I am talking for this issue.
Description of Patch
This patch contents changes for 3 issues involved to support duplicate nulls
1. Treat nulls as unequal while inserting
I have modified ControlRow class of BTree access. Now while searching the
tree to look for duplicate nulls are not treated as equal. If all the fields of
the key finds a match and one of the part is null it returns -1 or 1 depending
on the if ascending or descending order is requested.
2. Assume multiple rows in result if the where clause has is null for any of
the key part.
I have modified FromBaseTable to support his.
3. While creating index on an existing table don't consider nulls as equal for
they key parts.
I have modified DataType class and DataValueDescriptor interface and added
additional method to compare where a flag can be passed to specify if nulls are
to be treated equal while comparing. I have also modified
CreateIndexConstantAction so that it sets a flag in MergeInserter so that it
treats nulls as unequal by passing in this flag to MergeSort and SortBuffer
classes.
This patch is incomplete. The main issues are
1. Right now distinct clause is dropped whe the search is being performed on a
unique index. I need to modify optimizer to not to drop it if the distinct is
on unique index (to eliminate duplicate nulls)
2. Dropping a table fails.
> 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 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.
