db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: Derby, SQL 2003, UNIQUE constraints, unique indices and NULL (long)
Date Thu, 03 Nov 2005 19:09:43 GMT

> > CREATE TABLE t2 (i INTEGER, j INTEGER NOT NULL);
> > CREATE INDEX idx3 on t2(i);
> > CREATE UNIQUE INDEX idx4 on t2(j);
> >
> > SELECT i FROM t2;  -- use index idx3 (covers query)
> > SELECT j FROM t2;  -- Do a table scan
>
>Shouldn't the last SELECT use idx4 since the index is UNIQUE *and* j
>is NOT NULL?
>--
>Bernt Marius Johnsen, Database Technology Group,

Yes, you're right. I should have said:

CREATE TABLE t2 (i INTEGER, j INTEGER);
CREATE INDEX idx3 on t2(i);
CREATE UNIQUE INDEX idx4 on t2(j);

SELECT i FROM t2;  -- use index idx3 (covers query)
SELECT j FROM t2;  -- Do a table scan

I've done a little more thinking about this problem. I suspect that 
the store code uses the results of the same compare() method 
invocation to determine where to insert a row into an index and also 
to determine whether the row is a duplicate in a unique index. If 
Derby is to allow multiple nulls in a unique index, the store must 
use the two different null semantics for the two cases. For the 
question of where to insert the row, nulls must be considered to be 
equal to each other, and for the question of whether they are 
duplicates, they must be considered not equal to each other.

If I'm right, it should be easy to change Derby to allow multiple 
nulls in unique indexes. We still haven't heard from any of the store 
experts, though. Mike?

                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 


Mime
View raw message