You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Oyvind Bakksjo <ba...@google.com> on 2006/03/21 12:11:41 UTC
Unique constraints and null
Hi folks, long time no see. :)
I think I'm ready to start doing some work on Derby again (not full time,
but more than zero time ;o). I was thinking of DERBY-653, which Rick H
created and then closed himself during a discussion in October. I think it
would be nice if Derby behaved correctly according to the SQL standard here.
I see allowing nulls with unique constraints as a useful feature to have.
So, considering that I've been absent for quite a while, I don't know the
state of the nation and have to ask: Is this a feature the community would
like to have? Does anyone object to Derby behaving this way? And is anyone
working on this right now, considering working on it, or would like to?
--
Øyvind Bakksjø
Re: Unique constraints and null
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Oyvind Bakksjo wrote (2006-03-21 12:11:41):
> Hi folks, long time no see. :)
>
> I think I'm ready to start doing some work on Derby again (not full time,
> but more than zero time ;o). I was thinking of DERBY-653, which Rick H
> created and then closed himself during a discussion in October. I think it
> would be nice if Derby behaved correctly according to the SQL standard here.
> I see allowing nulls with unique constraints as a useful feature to have.
> So, considering that I've been absent for quite a while, I don't know the
> state of the nation and have to ask: Is this a feature the community would
> like to have? Does anyone object to Derby behaving this way? And is anyone
> working on this right now, considering working on it, or would like to?
+1 & Welcome back!
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Unique constraints and null
Posted by Andreas Korneliussen <An...@Sun.COM>.
Oyvind Bakksjo wrote:
> Hi folks, long time no see. :)
>
Hi Øyvind! Great to have you back
> I think I'm ready to start doing some work on Derby again (not full
> time, but more than zero time ;o). I was thinking of DERBY-653, which
> Rick H created and then closed himself during a discussion in October. I
> think it would be nice if Derby behaved correctly according to the SQL
> standard here. I see allowing nulls with unique constraints as a useful
> feature to have. So, considering that I've been absent for quite a
> while, I don't know the state of the nation and have to ask: Is this a
> feature the community would like to have? Does anyone object to Derby
> behaving this way? And is anyone working on this right now, considering
> working on it, or would like to?
>
I think this is a useful feature. I do not know of anyone who is working
on this issue.
Andreas
> --
> Øyvind Bakksjø
>
Re: Unique constraints and null
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I agree that these 2 approaches should not affect performance of the
index. I only bring it up as I once looked at a third option which
put all the work in the index, which involved sometimes treating
the nulls as different and sometimes the same - as you can imagine
it added some ugly special case code. I gave up on the approach as
I saw what it did to a lot of the basic current assumptions, and would
affect throughput of the normal path through the code.
Bernt M. Johnsen wrote:
>>>>>>>>>>>>>Mike Matrigali wrote (2006-03-22 11:22:39):
>>
>>I am not aware of any work being done. My only concern is that such an
>>implementation does not the affect the performance of existing index
>>operations. I would be interested in knowing what approach you are
>>considering to address it.
>
>
> Some thoughts: Basically I can see two approaches
> 1) Ignore null values in the index. Should be straight forward in the
> optimizer to know when to use the index or not. Downside: When there
> are multiple columns in the index, there are more cases when the index
> is unisable.
> 2) Put the nulls into the index. We would then have to consider nulls
> unequal to nulls when checking the constraint, but keep null equal to
> null when inserting into the B-trees.
>
> I can't see any reason (except bugs, of course) that a proper
> implementation should affect performance for existing index
> functionality.
>
Re: Unique constraints and null
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Mike Matrigali wrote (2006-03-22 11:22:39):
> I am not aware of any work being done. My only concern is that such an
> implementation does not the affect the performance of existing index
> operations. I would be interested in knowing what approach you are
> considering to address it.
Some thoughts: Basically I can see two approaches
1) Ignore null values in the index. Should be straight forward in the
optimizer to know when to use the index or not. Downside: When there
are multiple columns in the index, there are more cases when the index
is unisable.
2) Put the nulls into the index. We would then have to consider nulls
unequal to nulls when checking the constraint, but keep null equal to
null when inserting into the B-trees.
I can't see any reason (except bugs, of course) that a proper
implementation should affect performance for existing index
functionality.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Unique constraints and null
Posted by Mike Matrigali <mi...@sbcglobal.net>.
I am not aware of any work being done. My only concern is that such an
implementation does not the affect the performance of existing index
operations. I would be interested in knowing what approach you are
considering to address it.
Oyvind Bakksjo wrote:
> Hi folks, long time no see. :)
>
> I think I'm ready to start doing some work on Derby again (not full
> time, but more than zero time ;o). I was thinking of DERBY-653, which
> Rick H created and then closed himself during a discussion in October. I
> think it would be nice if Derby behaved correctly according to the SQL
> standard here. I see allowing nulls with unique constraints as a useful
> feature to have. So, considering that I've been absent for quite a
> while, I don't know the state of the nation and have to ask: Is this a
> feature the community would like to have? Does anyone object to Derby
> behaving this way? And is anyone working on this right now, considering
> working on it, or would like to?
>
> --
> Øyvind Bakksjø
>
Re: Unique constraints and null
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Øyvind,
Welcome back! Delighted to see you have not escaped our gravitational pull!
Personally, I don't have any strong feelings about this JIRA, but it is
grounded in the real customer experience of Dan Meany. For the record,
the relevant section of the SQL 2003 spec is Part 2, section 11.7.
Cheers,
-Rick
Oyvind Bakksjo wrote:
> Hi folks, long time no see. :)
>
> I think I'm ready to start doing some work on Derby again (not full
> time, but more than zero time ;o). I was thinking of DERBY-653, which
> Rick H created and then closed himself during a discussion in October.
> I think it would be nice if Derby behaved correctly according to the
> SQL standard here. I see allowing nulls with unique constraints as a
> useful feature to have. So, considering that I've been absent for
> quite a while, I don't know the state of the nation and have to ask:
> Is this a feature the community would like to have? Does anyone object
> to Derby behaving this way? And is anyone working on this right now,
> considering working on it, or would like to?
>
> --
> Øyvind Bakksjø
>
Re: Feature freeze vs bug fix freeze (was Re: Unique constraints and
null)
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi David,
Yes, that is my understanding. I don't know any other way to address
defects discovered during the stabilization/trashathon period.
Regards,
-Rick
David W. Van Couvering wrote:
> I was wondering if I could get some clarity here. Feature freeze is
> soon approaching, but won't we be able to do bugfixes after feature
> freeze?
>
> Thanks,
>
> David
>
> Kathey Marsden wrote:
>
>> Oyvind Bakksjo wrote:
>>
>>
>>> I think I'm ready to start doing some work on Derby again (not full
>>> time,
>>> but more than zero time ;o).
>>>
>>
>> Welcome back Oyvind! Feature freeze for 10.2 is fast approaching . I
>> hope you will also consider picking up a bug or two. According to
>> Jira, we currently have 155 outstanding code Bugs. My personal
>> recommendation this evening would be.
>> *
>> *http://issues.apache.org/jira/browse/DERBY-1085
>> java.lang.NullPointerException with char for bit data column and
>> subquery
>>
>> Also lots more goodies at:
>>
>> See https://issues.apache.org/jira/secure/ManageFilters.jspa
>> Derby open code bugs. 155
>> Derby Regression Test Failures 19
>> Derby JIRA Issues with patch available 18
>>
>>
>> Thanks
>>
>> Kathey
>>
>>
>>
>>
>>
>>
>>
>>
>>
Feature freeze vs bug fix freeze (was Re: Unique constraints and null)
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
I was wondering if I could get some clarity here. Feature freeze is
soon approaching, but won't we be able to do bugfixes after feature freeze?
Thanks,
David
Kathey Marsden wrote:
> Oyvind Bakksjo wrote:
>
>
>>I think I'm ready to start doing some work on Derby again (not full time,
>>but more than zero time ;o).
>>
>
> Welcome back Oyvind! Feature freeze for 10.2 is fast approaching . I
> hope you will also consider picking up a bug or two. According to
> Jira, we currently have 155 outstanding code Bugs. My personal
> recommendation this evening would be.
> *
> *http://issues.apache.org/jira/browse/DERBY-1085
> java.lang.NullPointerException with char for bit data column and subquery
>
> Also lots more goodies at:
>
> See https://issues.apache.org/jira/secure/ManageFilters.jspa
> Derby open code bugs. 155
> Derby Regression Test Failures 19
> Derby JIRA Issues with patch available 18
>
>
> Thanks
>
> Kathey
>
>
>
>
>
>
>
>
>
Re: Unique constraints and null
Posted by Kathey Marsden <km...@sbcglobal.net>.
Oyvind Bakksjo wrote:
>
>I think I'm ready to start doing some work on Derby again (not full time,
>but more than zero time ;o).
>
Welcome back Oyvind! Feature freeze for 10.2 is fast approaching . I
hope you will also consider picking up a bug or two. According to
Jira, we currently have 155 outstanding code Bugs. My personal
recommendation this evening would be.
*
*http://issues.apache.org/jira/browse/DERBY-1085
java.lang.NullPointerException with char for bit data column and subquery
Also lots more goodies at:
See https://issues.apache.org/jira/secure/ManageFilters.jspa
Derby open code bugs. 155
Derby Regression Test Failures 19
Derby JIRA Issues with patch available 18
Thanks
Kathey