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 Fri, 14 Sep 2007 05:38:32 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-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.


Mime
View raw message