Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1833894617.1234574099684.JavaMail.jira@brutus>
Date: Fri, 13 Feb 2009 17:14:59 -0800 (PST)
From: "Mike Matrigali (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4028) two rows can be inserted with the same
 value in a column that a unique constraint on that column should prevent
In-Reply-To: <141397733.1232637359997.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-4028?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-4028:
----------------------------------

    Derby Categories: [Deviation from standard, High Value Fix, Wrong query result]  (was: [Wrong query result, High Value Fix, Deviation from standard])

I have not had time to debug this or review any of the patches.  

Just wanted to say what I believe the assumptions are for the btree, so that we don't add the
wrong fixes.  

A btree must be able to uniquely identify every row in the tree, it does this by comparing some
number of the columns in the tree.  In the case of a unique btree it does this by comparing all
columns except for last one.  In the case of a non-unique btree it does this by comparing all
collumns including the last one (which is the row id pointer of the base table).  To uniquely identify
this row, whether it is deleted or not does not matter, there is meant to be code in the case of
deleted rows to wait around to make sure there is only one value with respect to these cases.

For the new feature which allows a unique constraint, but allows multiple nulls - the implementation is meant to use a "non-unique" btree.  This means that rows, including deleted 
rows are allowed to be duplicate.   At insert time there is meant to be new code that allows duplicate null values but not other duplicates.  I assume there is a bug in that new code.

> two rows can be inserted with the same value in a column that a unique constraint on that column should prevent
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4028
>                 URL: https://issues.apache.org/jira/browse/DERBY-4028
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.4.1.3
>         Environment: Windows XP
>            Reporter: Glenn Bradford
>         Attachments: check-deleted.diff
>
>
> The following DDL allows two rows to be inserted with the same value in a column when a unique constraint on that column should prevent it. The select statement (see the end of the mail) produces:
> ij> ALBUMID             |RANK       |YEARRELEAS&|ALBUM
> --------------------------------------------------------------------------------
> -----------------------------------------------------------------
> 11100               |1          |1945       |
> 13300               |1          |1966       |
> 2000                |7          |1974       |Songs in the Key of Life
> 88000               |12         |1971       |
> 4 rows selected
> The first two rows have the same rank value of 1 despite there being a unique constraint on that column.
> derby version: 10.4.1.3
> Bryan Pendleton reproduced this and suggested that the problem "is related to the fairly new feature of Derby
> which allows definition of a unique constraint on a null-able column".
> https://issues.apache.org/jira/browse/DERBY-3330
> Redefining the rank column as 'not null' made the problem go away.
> I came across this after running a program that randomly makes inserts, updates, and deletes into this table. It usually takes between 500-600 DDL statements to make it happen. I then took the results and hand-pruned out as many statements as I could and tried to minimize the number of rows produced by the select statement, while still reproducing the issue. At this point it is very sensitive to any changes. For example, re-running the test after removing what appear to be redundantly inserted rows will make the problem go away, as will modifications to band and album names. It's all very strange.
> A very old version of Cloudscape (3.6.9), from which I am trying to upgrade, does not have this problem.
> -------------------------------------------------------------------------
> drop table tra;
> create table tra (
>     albumId bigint,
>     rank int,
>     CONSTRAINT UNIQUE_RANK
>         UNIQUE(rank),
>     band varchar(100),
>     album varchar(100),
>     yearReleased int,
>     CONSTRAINT PK_TOPROCKALBUMS
>         PRIMARY KEY(albumId)
> );
> insert into tra values(1000, 1, '', '', 1966);
> insert into tra values(2000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(3000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(4000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(5000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(6000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(7000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(8000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(9000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(13000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(14000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(15000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(16000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(17000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(18000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(19000, 14, 'Joni ', 'Blue', 1971);
> insert into tra values(20000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(21000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(22000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(23000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(24000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(25000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(26000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(27000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(28000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(29000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(30000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(31000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(32000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(33000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(34000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(36000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(36000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(37000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(38000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(39000, 1, 'The Beatles', '', 1966);
> insert into tra values(40000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(41000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(42000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(43000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(44000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(45000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(46000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(47000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(48000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(49000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(50000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(51000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(52000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(53000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(54000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(55000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(56000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(57000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(59000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(60000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(61000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(62000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(63000, 1, 'The Beatles', '', 1966);
> delete from tra where rank=1;
> insert into tra values(64000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(65000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(66000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(67000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(68000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(69000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(70000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(71000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(72000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(73000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(74000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(75000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(76000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(77000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(78000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(79000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> delete from tra where rank=14;
> insert into tra values(80000, 14, 'Joni Mitchell', 'Blue', 1971);
> insert into tra values(81000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(82000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(83000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(84000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(85000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(86000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(87000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(88000, 12, '', '', 1971);
> insert into tra values(89000, 1, 'The Beatles', '', 1966);
> insert into tra values(90000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(91000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(92000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(93000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(94000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(95000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(96000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(97000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(98000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(99000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10100, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10200, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10300, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10400, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10500, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> delete from tra where rank=1;
> insert into tra values(10600, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10700, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10800, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(10900, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11100, 1, 'The Beatles', '', 1966);
> insert into tra values(11200, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11300, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11400, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11500, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11600, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11700, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11800, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(11900, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12100, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12200, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> delete from tra where rank=14;
> insert into tra values(12300, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12400, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> update tra set yearReleased=1945 where rank=1;
> insert into tra values(12500, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12600, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12700, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12800, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(12900, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(13000, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(13100, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(13200, 7, 'Stevie Wonder', 'Songs in the Key of Life', 1974);
> insert into tra values(13300, 1, 'The Beatles', '', 1966);
> select albumId, rank, yearReleased, album from tra order by rank;
> exit;

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