From derby-user-return-4383-apmail-db-derby-user-archive=db.apache.org@db.apache.org Mon Jun 12 10:22:14 2006 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 61185 invoked from network); 12 Jun 2006 10:22:13 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 12 Jun 2006 10:22:13 -0000 Received: (qmail 8322 invoked by uid 500); 12 Jun 2006 10:22:12 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 8072 invoked by uid 500); 12 Jun 2006 10:22:12 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 8050 invoked by uid 99); 12 Jun 2006 10:22:12 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jun 2006 03:22:11 -0700 X-ASF-Spam-Status: No, hits=0.3 required=10.0 tests=NO_OBLIGATION,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [192.18.1.36] (HELO gmpea-pix-1.sun.com) (192.18.1.36) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Jun 2006 03:22:10 -0700 Received: from d1-emea-02.sun.com (d1-emea-02.sun.com [192.18.2.112] (may be forged)) by gmpea-pix-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id k5CALnLW002873 for ; Mon, 12 Jun 2006 11:21:49 +0100 (BST) Received: from conversion-daemon.d1-emea-02.sun.com by d1-emea-02.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) id <0J0Q00B01S2ULP00@d1-emea-02.sun.com> (original mail from Bernt.Johnsen@Sun.COM) for derby-user@db.apache.org; Mon, 12 Jun 2006 11:21:49 +0100 (BST) Received: from localhost ([129.159.112.201]) by d1-emea-02.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0J0Q00JIUSSBPN60@d1-emea-02.sun.com>; Mon, 12 Jun 2006 11:21:48 +0100 (BST) Date: Mon, 12 Jun 2006 12:21:47 +0200 From: "Bernt M. Johnsen" Subject: Re: "generated by default" question In-reply-to: <20060609160003.E4D27251AA@dbrack01.segel.com> Sender: Bernt.Johnsen@Sun.COM To: Derby Discussion , msegel@segel.com Cc: jta@bristowhill.com Message-id: <20060612102147.GB17297@atum01.norway.sun.com> Organization: Sun Microsystems MIME-version: 1.0 Content-type: multipart/signed; boundary=HlL+5n6rz5pIUxbD; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-disposition: inline References: <448994B1.1020907@bristowhill.com> <20060609160003.E4D27251AA@dbrack01.segel.com> User-Agent: Mutt/1.5.10i X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --HlL+5n6rz5pIUxbD Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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=20 INCREMENT BY 1=20 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 =20 Function =20 Generate and return the next value of a sequence generator. =20 Syntax Rules =20 None. =20 Access Rules =20 None. =20 General Rules =20 1) Let SEQ be the SEQUENCE specified in an application of this Subclause. =20 2) Let DT, CBV, INC, SMAX, and SMIN be the data type, current base value, increment, maximum value and minimum value, respectively, of SEQ. =20 3) If there exists a non-negative integer N such that SMIN <=3D CBV + N * INC <=3D SMAX and the value (CBV + N * INC) has not already been returned in the current cycle, then let V1 be (CBV + N * INC). Otherwise, =20 Case: =20 a) If the cycle option of SEQ is NO CYCLE, then an exception condition is raised: data exception - sequence generator limit exceeded. =20 b) Otherwise, a new cycle is initiated. =20 Case: =20 i) If SEQ is an ascending sequence generator, then let V1 be SMIN. =20 ii) Otherwise, let V1 be SMAX. =20 4) Case: =20 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. =20 b) Otherwise, the current base value of SEQ is set to the highest non-issued value in the cycle. =20 5) V1 is returned as the RESULT. =20 Conformance Rules =20 None. >>>>>>>>>>>> Michael Segel wrote (2006-06-09 10:58:25): > There is a Jira issue already opened.=20 > Someone pointed to an earlier thread in which Daniel already recommended a > solution. >=20 > I don't find fault with Craig's comment. > I was thinking in the debate between myself and Bernt. >=20 > 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. >=20 > 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...) >=20 > 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 sell= ing > support for Cloudscape/Derby/JavaDB, then they should step up to the plate > and fix the problem. >=20 > 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.... >=20 >=20 > > -----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 > >=20 > > Michael Segel wrote: > > > > > Having said that, Daniel already made a suggestion which is in use to= day > > by > > > other databases.... > > > > > > The simplest solution would be to find the MAX value, rest the sequen= ce > > 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 > >=20 > > Craig Russell wrote earlier in this thread [1]: > >=20 > > > 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. > >=20 > > Anyone is welcome to open a Jira issue. Jira assistance is at > > http://db.apache.org/derby/DerbyBugGuidelines.html . > >=20 > > -jean > >=20 > > [1] > > http://mail-archives.apache.org/mod_mbox/db-derby- > > user/200605.mbox/%3cDD522070-780A-4C31-B095-0009E49EFEA2@SUN.com%3e >=20 >=20 --=20 Bernt Marius Johnsen, Database Technology Group,=20 Staff Engineer, Technical Lead Derby/Java DB Sun Microsystems, Trondheim, Norway --HlL+5n6rz5pIUxbD Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2.2 (GNU/Linux) iD8DBQFEjUA7lFBD9TXBAPARAn5MAJ9/abn6S/Yi0aPzSuUHg/XjTFaqQgCgjGKX CuDEKEOTNj/5ZWeh33jDju4= =SIjv -----END PGP SIGNATURE----- --HlL+5n6rz5pIUxbD--