db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øystein Grøvlen (JIRA) <j...@apache.org>
Subject [jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
Date Tue, 16 Oct 2007 11:31:54 GMT

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

Øystein Grøvlen commented on DERBY-2212:
----------------------------------------

Anurag Shekhar (JIRA) wrote:
> Partial key matching is used only for searching. While searching
> (either for update or select) null should be treated equal. So this
> code shouldn't get executed.

It concerns me a bit that methods with generic names like 'compare'
makes assumption about in what context they are used.  If
treatNullsEqual is false, I think nulls should not be treated as equal
regardless of its current use.  If the caller wants nulls to be
treated equal it should pass in true for treatNullsEqual.

> nullsOrderedLow is used for ordering nulls with respect to not null
> values (in order by clause with null ordering option) so when two
> nulls are being compared this flag is not relevant.
> 
> -1 or 1 result of the null comparison will only effect where new
> node will be inserted (left or right of the existing node). The spec
> in my opinion doesn't mandates it. So I think its ok to return
> either -1 or 1. Please let me know if I am wrong.

I think you are right about the effect on current usage, but again, this is
a generic method for comparison of data values, and even if its
current usage is limited to insertion into B-tree, I do not think this
code should make such an assumption.


> 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