db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: "generated by default" question
Date Mon, 12 Jun 2006 10:21:47 GMT
Well, I'm the one that doesn't accept this as a bug/defect since Derby
behaves according to the SQL standard. My stand is based on the
chapters 4.14.17 and 9.21 in the SQL 2003 (INCITS/ISO/IEC 9075-2-2003
Information technology - Database languages - SQL - Part 2: Foundation
(SQL/Foundation)), see below.

The identity column as specified in the standard does not guarantee a
unique value. It gives you a value according to a sequence
generator. If used properly, then the user may get unique values, but
he may also generate duplicates or violate constraints and get an
exception.

You may even write a cyclical identity column like this

GENERATED BY DEFAULT AS IDENTITY (START WITH 0 
                                  INCREMENT BY 1 
                                  MAXVALUE 2
                                  CYCLE)

Here the generated values for the identity column will be
0,1,2,0,1,2,0,1,2,0,1,2.......  (better not have a UNIQUE constraint
on the identity column :-)

(Note: Derby does not support MAXVALUE and CYCLE. Derby does also
require a comma befor INCREMENT).

Chapter 4.14.17:

    The columns of a base table BT can optionally include not more than
    one identity column. The declared type of an identity column is either
    an exact numeric type with scale 0 (zero), INTEGER for example, or a
    distinct type whose source type is an exact numeric type with scale 0
    (zero). An identity column has a start value, an increment, a maximum
    value, a minimum value, and a cycle option. 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.
    SG.


And chapter 9.21:

    9.21 Generation of the next value of a sequence generator
    
    Function
    
    Generate and return the next value of a sequence generator.
    
    Syntax Rules
    
       None.
    
    Access Rules
    
       None.
    
    General Rules
    
    1) Let SEQ be the SEQUENCE specified in an application of this
       Subclause.
    
    2) Let DT, CBV, INC, SMAX, and SMIN be the data type, current base
       value, increment, maximum value and minimum value, respectively, of
       SEQ.
    
    3) If there exists a non-negative integer N such that SMIN <= CBV + N
       * INC <= SMAX and the value (CBV + N * INC) has not already been
       returned in the current cycle, then let V1 be (CBV + N *
       INC). Otherwise,
    
       Case:
    
       a) If the cycle option of SEQ is NO CYCLE, then an exception
          condition is raised: data exception - sequence generator limit
          exceeded.
    
       b) Otherwise, a new cycle is initiated.
    
            Case:
    
            i) If SEQ is an ascending sequence generator, then let V1 be
               SMIN.
    
            ii) Otherwise, let V1 be SMAX.
    
    4) Case:
    
       a) If SEQ is an ascending sequence generator, the current base
          value of SEQ is set to the value of the lowest non-issued value
          in the cycle.
    
       b) Otherwise, the current base value of SEQ is set to the highest
          non-issued value in the cycle.
    
    5) V1 is returned as the RESULT.
    
    Conformance Rules
    
       None.


>>>>>>>>>>>> Michael Segel wrote (2006-06-09 10:58:25):
> There is a Jira issue already opened. 
> Someone pointed to an earlier thread in which Daniel already recommended a
> solution.
> 
> I don't find fault with Craig's comment.
> I was thinking in the debate between myself and Bernt.
> 
> The issue is that while you can look towards the standards on sequences,
> when you have an identity column that auto-generates a value, you have to
> account for the fact that someone can insert a row that has an unused
> identity value and that the sequence hasn't generated a value that large
> yet.
> 
> It's a bug if you ask for Derby to generate an identity value and then it
> fails because the sequence returns a value that is already in use.
> (I.e. someone inserts a row with the identity value of 10 and the sequence
> is only at 6. So that when the sequence returns a value of 10, the insert
> will fail...)
> 
> My point is that there is a recognized problem, and it's a defect. Since
> there are two well established corporations which are profiting from selling
> support for Cloudscape/Derby/JavaDB, then they should step up to the plate
> and fix the problem.
> 
> It would be different if we were looking for an enhacement or a feature
> request. Then there would be no obligation to implement the
> enhancement/feature....
> 
> 
> > -----Original Message-----
> > From: Jean T. Anderson [mailto:jta@bristowhill.com]
> > Sent: Friday, June 09, 2006 10:33 AM
> > To: Derby Discussion
> > Subject: Re: "generated by default" question
> > 
> > Michael Segel wrote:
> > <snip>
> > > Having said that, Daniel already made a suggestion which is in use today
> > by
> > > other databases....
> > >
> > > The simplest solution would be to find the MAX value, rest the sequence
> > to
> > > MAX() +1 and re-insert the row.
> > >
> > > There are other possible solutions.
> > >
> > > It's disappointing that certain people don't accept this as a bug
> > 
> > Craig Russell wrote earlier in this thread [1]:
> > 
> > > 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.
> > 
> > Anyone is welcome to open a Jira issue. Jira assistance is at
> > http://db.apache.org/derby/DerbyBugGuidelines.html .
> > 
> >  -jean
> > 
> > [1]
> > http://mail-archives.apache.org/mod_mbox/db-derby-
> > user/200605.mbox/%3cDD522070-780A-4C31-B095-0009E49EFEA2@SUN.com%3e
> 
> 

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

Mime
View raw message