db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: "generated by default" question
Date Wed, 14 Jun 2006 14:23:45 GMT


First, I'd like to thank you for bringing the argument in to a concise and
orderly fashion.

My comments are mixed in with yours and in the most part we are in

However, there are differences of opinion and this still should be
considered either a bug/product defect, or a design defect. Here's Why:

If there exists a value N that is an element of the solution set to 9.21's
paragraph 3, and that the use of this value results in a successful
insertion in to the table, then this value of N should be used.

In simple and plain English:
   The sequence should return a unique value (one that hasn't been used
before) such that the insert should not fail due to the identity column's
non-unique constraint.

An example:
We have an identity column with "GENERATE BY DEFAULT".
Sequence starts at 1. You enter 4 rows using the DEFAULT values. (1,2,3,4).
You then manually insert a row with a value of 5.

You attempt to insert a row using the DEFAULT value.
Currently Derby throws an SQL Exception because the insert violated the
Identity column's uniqueness constraint.

This is a bug/product defect or design defect because within the solution
set for N, there are other numbers which would not yield an error.

For example, had the sequence returned the value 6, which is a valid value
btw, then there would not be an error. 

Also consider this...
The anticipated result of inserting a row using the generated value for an
identity column should be a successful insertion, as long as there is room
in the table. (That there exists a value for N in 9.21's paragraph 3).

Because this does not occur, it is a bug.

The solution is fairly trivial.

On an insert in to a table which has an identity column which specifies
  If the row to be inserted contains a value for the identity column,
      After a successful insert,
          Assign the CBV to that value just inserted.

That's it.

Using the previous example,
You manually insert a row using the value 10;
If the row can be successfully inserted in to the table,
Then you reset the CBV to 10. 
Then when you next insert a row using the default value, it will insert at

> -----Original Message-----
> From: Bernt.Johnsen@Sun.COM [mailto:Bernt.Johnsen@Sun.COM]
> Sent: Wednesday, June 14, 2006 6:07 AM
> To: Derby Discussion
> Subject: Re: "generated by default" question
> Let me clearify some items from the SQL 2003 standard related to the
> latest mails regarding this issue from Craig and Michael:
> 1) In the case of "generated always", it should not be possible to
>    insert explicit values in identity columns, nor to alter generated
>    values.
> 2) Internal and external sequence generators are by default not
>    cyclical (Ch 9.22), so an exception will occur when they are
>    exhausted (Ch 9.21)
This is the first time anyone published a reference to 9.22. (Sorry but my
company is too cheap right now to pay for an electronic copy of the spec...
So lets not worry about cyclical sequence generators.

> 3) In the case of a rollback, the sequence generator does not "skip"
>    values, altough it may appear so. The standard says that "commits
>    and rollbacks of SQL-transactions have no effect on the current
>    base value of a sequence generator". (Ch 4.21.1) It is the *use* of
>    the generated value that is rolled back.
This was never really an issue, however it is important to point out that
the mathematical definition of the sequence generator allows for the
sequence number to return any value N that fits the criteria in 9.21.

> 4) A sequence generator which for some reason skips a value (which is
>    allowed), will not issue that value in the current cycle, since the
>    current base value will be set to the highest value (or lowest if
>    the generator is descending) issued in that cycle (Ch 9.21, general
>    rules, part 4)

Now, for those that are mathematically challenged, the fact that you can
skip a value in a sequence is a very important component to its definition.
Mathematics is a very precise language. At least that is what my uncle kept
trying to pound in to my thick skull. ;-)

This key fact is why you can consider the current implementation of Derby
has either a product defect or a design defect that needs to be corrected!

> Then again:
> 5) The behaviour of sequence generators is described independently of
>    the context they are used in (as Craig correctly points out but with
>    other words).
Correct. The specification describes how the sequence generators work in
general. The specification describes how the identity column works in
Craig used the term "orthogonal".  While each component may be orthogonal to
each other, they are combined to deliver a function/feature of the database.
Its like having a plate of Fish and Chips. There's the fish and then there's
the chips. Each is orthogonal to each other however; when they are combined,
with a pint of your favorite brew to create what we call "dinner". ;-)

The key point is that you're implementing a database feature using
orthogonal components and you have to consider the context when deciding if
you are in fact following the standard. 

> 6) I can find no relation, whatsoever, defined in the standard between
>    the existing values in a column and how the internal sequence
>    generator of an identity column behaves.
Correct. Nor would you. That "gap" is left to the interpretation of the
database designer. It's very possible to be "compliant" with a standard, yet
offer a substandard product. (Thus we debunk the myth of the database as a
commodity product....)

However, that does not mean that the implementation of the sequence
generator is compliant. 

> And finally:
> 7) How other databases (or Derby for that matter) implement a feature
>    is no proof of standards compliancy.
Sure enough. However, this has been pointed out countless times that its
appropriate to compare feature functionality with comparable products in an
effort to produce a better design. 

> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway

But hey! What do I know?
Its not like I have a degree in Software Engineering or anything. ;-)


Michael Segel, 
Chief Peon in Charge,
MSC Corp.
Chicago, IL, USA

View raw message