Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <64649984.1118792387172.JavaMail.jira@ajax.apache.org>
Date: Wed, 15 Jun 2005 01:39:47 +0200 (CEST)
From: "Daniel John Debrunner (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-359) GENERATED BY DEFAULT identity option
 should consume identity values even if the transaction aborts. This would
 allow skipping over user inserted values during system generation.
In-Reply-To: <1685791433.1118776008808.JavaMail.jira@ajax.apache.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-359?page=comments#action_12313658 ] 

Daniel John Debrunner commented on DERBY-359:
---------------------------------------------

I would say the the first failed INSERT is not an expected error but in the same situation as all the other subsequent INSERTs that fail. If this is fixed then I would expect the insert into tauto(k) values 4 to succeed with some unique value for i.

> GENERATED BY DEFAULT identity option should consume identity values even if the transaction aborts. This would allow skipping over user inserted values during system generation.
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-359
>          URL: http://issues.apache.org/jira/browse/DERBY-359
>      Project: Derby
>         Type: Improvement
>   Components: SQL
>     Versions: 10.1.0.0
>  Environment: Generic
>     Reporter: Satheesh Bandaram

>
> Using GENERATED BY DEFAULT identity column, user can specify a value to be inserted into identity column. When the system generated value and this user specified value match, if an unique index is present, an error is correctly generated. However, it is desirable to skip this value for next identity generation, so that next INSERT would pass. Currently, all subsequent insert statements fail.
> ij(CONNECTION0)> create table tauto(i int generated by default as identity, k int);
> 0 rows inserted/updated/deleted
> ij(CONNECTION0)> create unique index tautoInd on tauto(i);
> 0 rows inserted/updated/deleted
> ij(CONNECTION0)> insert into tauto(k) values 1,2;
> 2 rows inserted/updated/deleted
> ij(CONNECTION0)> select * from tauto;
> I          |K
> -----------------------
> 1          |1
> 2          |2
> 2 rows selected
> ij(CONNECTION0)> insert into tauto values (4,4);
> 1 row inserted/updated/deleted
> ij(CONNECTION0)> insert into tauto(k) values 3;
> 1 row inserted/updated/deleted
> ij(CONNECTION0)> insert into tauto(k) values 4;             <=== Expected error.
> ERROR 23505: The statement was aborted because it would have caused a duplicate
> key value in a unique or primary key constraint or unique index identified by 'T
> AUTOIND' defined on 'TAUTO'.
> ij(CONNECTION0)> insert into tauto(k) values 5;             <=== Would be preferable to skip over identity value of '4' and use '5'
> ERROR 23505: The statement was aborted because it would have caused a duplicate
> key value in a unique or primary key constraint or unique index identified by 'T
> AUTOIND' defined on 'TAUTO'.
> ij(CONNECTION0)> insert into tauto(k) values 6;
> ERROR 23505: The statement was aborted because it would have caused a duplicate
> key value in a unique or primary key constraint or unique index identified by 'T
> AUTOIND' defined on 'TAUTO'.
> ij(CONNECTION0)>
> At this point, all system generated identity value based inserts would continue to fail.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira