Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <6860617.1192108850711.JavaMail.jira@brutus>
Date: Thu, 11 Oct 2007 06:20:50 -0700 (PDT)
From: "Anurag Shekhar (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2212) Add "Unique where not null" to
 create index
In-Reply-To: <20254731.1167917367874.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


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

Anurag Shekhar commented on DERBY-2212:
---------------------------------------

thanks =C3=98ystein, for the review and trying out the patch.

1. I do not understand the reasoning behind the return values.  I notice th=
at if I swap them, dropping a table does not fail anymore (I have not check=
ed if that breaks other operations).

In this case return value (1) is not important. But the sign is important b=
ecause that determines the sequence of sorted keys (used while creating ind=
ex on a table with existing records. While creating the B Tree first time t=
he keys are supposed to be in a sequence and this is  verified by invoking =
method in ControlRow. So if there are duplicate nulls are present in table =
they should still confirm the sequence (depending on the flag passed ascOrD=
esc []).
Yes I saw the drop works fine if I change the sequence but in that case cre=
ating of index on table with existing records will fail.

I have found the problem which is causing the drop table to fail. Its happe=
ning while scanning catalog's table to remove table index. Some of the attr=
ibute in index record are null and while comparing these two nulls are trea=
ted unequal and the deleting fails. I making changes to fix this and will b=
e posting them after incorporating your other comments.

2 .What are the implications if there are several columns for which both ha=
ve null?  You only test ascOrDesc for the latest column.

I am changing this part of code. Now the first null will ensure a mismatch =
and ascOrDesc flag for that column will be used. But I think it shouldn't m=
atter for which null ascOrDesc is used as this is used only by Sorter and i=
t doesn't makes any difference in which order these two  record are sorted =
(at least till we have something like row id).

I will get back back about other questions.=20

> 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 wel=
l 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 rest=
rictive, does not completely comply with SQL standards (both letter and int=
ent) as well as with business needs and intending meaning of NULL values (2=
 null values are not considered as equal, this comparision shall return NUL=
L, and for selection criteria boolean null is treated as FALSE).
> This behavior, as far as I can see, is modelled after DB2 (and differs fr=
om 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 intro=
duced in Derby.
> Regards,
> Oleksandr Alesinskyy

--=20
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.