On 5/30/06, Michael Segel <derby@segel.com> wrote:
I tried to send this out earlier from a different machine...

I read the thread of articles, and unfortunately, you're wrong, or rather the
material that you posted regarding the spec does not match this issue.

From your earlier post:
-=-
I looked at the SQL 2000 spec to see what it says about generated keys.
Following are some lines copied from the Identity columns section and from
Sequence generators section. The value generation for identity column follows
the gules of Sequence generator. And in the Sequence generator section, the
spec says that value generation is done in a transaction of its own and is
not associated with the outside user transaction.

4.14.4 Identity columns

An identity column is associated with an internal sequence generator
SG. Let IC be the identity column of BT . When a row R is presented for
insertion into BT , if R does not contain a column corresponding to IC, then
the value V for IC in the row inserted into BT is obtained by applying the
General Rules of Subclause 9.21, ''Generation of the next value of a sequence
generator'', to SG . The definition of an identity column may specify
GENERATED ALWAYS or GENERATED BY DEFAULT.
[\n added by MJS]
4.21 Sequence generators

Changes to the current base value of a sequence generator are not controlled
by SQL-transactions; therefore, commits and rollbacks of SQL-transactions
have no effect on the current base value of a sequence generator.

-=-
First in 4.14.4 it references 9.21.

You include 4.21.

So either you looked at the wrong sub section or you have a typo.
 
It's not a wrong subsection or typo. I was copying only the relevant sections from SQL 2002 spec for the thread http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069  Section 4.21 had information on how sequence generation happens in it's own transactions.

Second...
4.21 doesn't talk about the issue at hand. What 4.21 talks about is that when
you implement a sequence generator, it needs to be outside of any
transactions.

Suppose thread 1 inserts 3 rows in to a table with an identity column.
Suppose thread 2 inserts 2 rows.
Thread 2 commits.
Thread 1 rollsback.

Suppose that the starting value of the identity column was one.
At the end of all transactions, there will be two rows in the table, with the
identity values of 4 and 5 respectively. The next value in the identity
sequence is 6.

That is what is meant by 4.21.

The issue that we see is that Derby barfs when it hits a row that was inserted
without using the identity value, and Derby doesn't know how to generate the
next identity value.
 
I would not claim that I went through entire SQL2002 spec to know this for sure but from what I can remember, I don't think the spec covers what should happen if a generated key is already used.

That sir, is a bug.
And no, the SQL 2000 spec, as presented in this discussion does not concur
with how derby is implementing their auto sequence generation.

So, when will either Sun or IBM fix this? ;-)

-G

On Tuesday 30 May 2006 11:02 am, Mamta Satoor wrote:
> Based on the thread
> http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GE
>NERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069 Derby
> behavior is SQL 2000 compatible.
>
> Mamta
>
> On 5/30/06, Craig L Russell <Craig.Russell@sun.com> wrote:
> > Hi G,
> >
> > Do you have a good solution that you can write up in detail and post
> > to JIRA? Then someone with an itch can fix it. Do-ocracy in action.
> >
> > Craig
> >
> > On May 30, 2006, at 7:38 AM, derby@segel.com wrote:
> > > Hmmm,
> > >
> > > Yeah, that's what I'm afraid of.
> > >
> > > Essentially what they are asking is that if you try to insert a row
> > > and it
> > > fails, you have two options.
> > >
> > > 1) Increment your count and try again (Restart) until you succeed,
> > > or, find
> > > the max value, and reset to max value + 1.
> > >
> > > The trouble in using #2, is that lets say you have rows 1,2,3,4,5
> > > entered,
> > > and someone manually enters 10, 1001, 1002, 1004. So when you
> > > search for the
> > > max value, you'll end up 1004, and restart with 1005.
> > >
> > > This is something that should be done behind the scenes.
> > >
> > > SO its not a "bug" but a design defect.
> > >
> > > -G
> > >
> > >> -----Original Message-----
> > >> From: news [mailto:news@sea.gmane.org] On Behalf Of hilz
> > >> Sent: Monday, May 29, 2006 4:19 PM
> > >> To: derby-user@db.apache.org
> > >> Subject: Re: "generated by default" question
> > >>
> > >> I wish it was a bug!
> > >> it seems this is by design, but i hope there is some other way to
> > >> overcome this behavior.
> > >>
> > >> here is a quote from the docs at
> > >>    http://db.apache.org/derby/docs/dev/ref/ref-single.html
> > >> that show it is by design:
> > >>
> > >> <quote>
> > >> RESTART WITH integer-constant specifies the next value to be
> > >> generated
> > >> for the identity column. RESTART WITH is useful for a table that
> > >> has an
> > >> identity column that was defined as GENERATED BY DEFAULT and that
> > >> has a
> > >> unique key defined on that identity column. Because GENERATED BY
> > >> DEFAULT
> > >> allows both manual inserts and system generated values, it is
> > >> possible
> > >> that manually inserted values can conflict with system generated
> > >> values.
> > >> To work around such conflicts, use the RESTART WITH syntax to specify
> > >> the next value that will be generated for the identity column.
> > >> Consider
> > >> the following example, which involves a combination of automatically
> > >> generated data and manually inserted data:
> > >>
> > >> CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
> > >> CREATE
> > >> UNIQUE INDEX tautoInd ON tauto(i) INSERT INTO tauto(k) values 1,2
> > >>
> > >> The system will automatically generate values for the identity
> > >> column.
> > >> But now you need to manually insert some data into the identity
> > >> column:
> > >>
> > >> INSERT INTO tauto VALUES (3,3) INSERT INTO tauto VALUES (4,4) INSERT
> > >> INTO tauto VALUES (5,5)
> > >>
> > >> The identity column has used values 1 through 5 at this point.  If
> > >> you
> > >> now want the system to generate a value, the system will generate
> > >> a 3,
> > >> which will result in a unique key exception because the value 3 has
> > >> already been manually inserted.  To compensate for the manual
> > >> inserts,
> > >> issue an ALTER TABLE statement for the identity column with
> > >> RESTART WITH
> > >> 6:
> > >>
> > >> ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6
> > >>
> > >> ALTER TABLE does not affect any view that references the table being
> > >> altered. This includes views that have an "*" in their SELECT
> > >> list. You
> > >> must drop and re-create those views if you wish them to return the
> > >> new
> > >> columns.
> > >> </quote>
> > >>
> > >> Michael Segel wrote:
> > >>> On Monday 29 May 2006 3:31 pm, hilz wrote:
> > >>> After a quick glance,
> > >>>
> > >>> This looks like a bug.
> > >>>
> > >>> You should be able to insert your own values in the ID column,
> > >>> which you
> > >>
> > >> do...
> > >>
> > >>> then on rows that are auto generated, they should chose an
> > >>> incremental
> > >>
> > >> value.
> > >>
> > >>> Using your example, it should have trapped the error and then
> > >>> tried to
> > >>
> > >> insert
> > >>
> > >>> using 2... until it found an integer value that was not in use.
> > >>>
> > >>> But hey, what do I know.
> > >>> I'm sure someone is going to tell me that this functionality is
> > >>> behaving
> > >>
> > >> per
> > >>
> > >>> spec....
> > >>>
> > >>> -G
> > >>>
> > >>>> Hi all.
> > >>>> If i have a table A defined as follows:
> > >>>>
> > >>>> create table A
> > >>>> (
> > >>>> ID     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> > >>>> NAME   VARCHAR(255)
> > >>>> );
> > >>>>
> > >>>> then i do the following:
> > >>>>
> > >>>>      insert into A (ID, NAME) values (1,'hello 1');
> > >>>>
> > >>>> and then i do the following:
> > >>>>
> > >>>>      insert into A (NAME) values ('hello 2');
> > >>>>
> > >>>> I will get this error:
> > >>>>
> > >>>> 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 'SQL060529010004440' defined on 'A'.
> > >>>>
> > >>>> To avoid this, I will have to do :
> > >>>>
> > >>>>      alter table....RESTART WITH....
> > >>>>
> > >>>> Is there another way to make the autoincrement smart enough to know
> > >>
> > >> that
> > >>
> > >>>> the value already exists and just generate a new value for me?
> > >>>> I find it odd to have to set the "restart with" to skip the
> > >>>> values that
> > >>>> i set manually.
> > >>>>
> > >>>> thanks for any help.
> >
> > Craig Russell
> > Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> > 408 276-5638 mailto:Craig.Russell@sun.com
> > P.S. A good JDO? O, Gasp!

--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]