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