: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 30, 2006 10:23 AM
To: Derby Discussion
Subject: Re: generated by default question
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
--- [EMAIL PROTECTED] wrote:
There is a very simple solution.
[solution details snipped]
This is the simplest solution, lowest cost solution,
and doesn't violate the
definition of an SQL sequence.
Tthis solution creates a couple different
problems that are probably unexpected by
-Original Message-
From: Daniel John Debrunner [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 29, 2006 1:37 PM
To: Derby Discussion
Subject: Re: generated by default question
--- [EMAIL PROTECTED] wrote:
There is a very simple solution.
[solution details snipped
[EMAIL PROTECTED] wrote:
[mjs]
Bzzzt!
No.
Already solved your first example. Just reset the sequence if the row has a
value in the identity column between CBV and MAX.
Oops! Guess that blows away your theory.
I don't have any theory, I was pointing out that the proposed very
simple
-Original Message-
From: Daniel John Debrunner [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 29, 2006 3:22 PM
To: Derby Discussion
Subject: Re: generated by default question
[EMAIL PROTECTED] wrote:
[mjs]
Bzzzt!
No.
Already solved your first example. Just reset
I'll second that as it sure does. We just switched from HSQLDB to Derby due
to Derby's better handling of isolation levels, but this issue is really
time consuming as we have to rewrite many of our tests.
Jimisola
--
View this message in context:
I know? ;-)
-G
-Original Message-
From: Jimisola Laursen [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 28, 2006 7:05 PM
To: derby-user@db.apache.org
Subject: Re: generated by default question
I'll second that as it sure does. We just switched from HSQLDB to Derby
due
Michael Segel wrote:
Look, if you grok the math, you'll see that if you return a result from the
sequence that causes an error, then you've got a bug, when there's another
number within the result set that doesn't cause the exception.
I don't know how you can call it a bug. The behavior is
On Friday 16 June 2006 1:29 am, Vic Ricker wrote:
Michael Segel wrote:
Look, if you grok the math, you'll see that if you return a result from
the sequence that causes an error, then you've got a bug, when there's
another number within the result set that doesn't cause the exception.
I
On 6/16/06, Michael Segel [EMAIL PROTECTED] wrote:
I think we'll just have to agree to disagree.
Whether you agree or disagree doesn't change the fact that this is a bug.
Whether or not this is a bug is not a 'fact'. That there is
disagreement would imply that such a determination lies more
by default question
On 6/16/06, Michael Segel [EMAIL PROTECTED] wrote:
I think we'll just have to agree to disagree.
Whether you agree or disagree doesn't change the fact that this is a
bug.
Whether or not this is a bug is not a 'fact'. That there is
disagreement would imply
Michael Segel wrote (2006-06-14 09:23:45):
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.
[mjs]
Correct. Nor would you. That gap is left to the
Sigh.
Don't they teach math in engineering anymore?
Lets try this one more time.
In 9.21, if N does exist, then N represents a solution set of potential
values.
In your interpretation, you are *implicitly* adding an additional boundary
that the sequence returns the MIN(N) regardless of the
Here is a different view to the problem.
I have a bulk export-import routine
that exports all the tables into .del files for backup using SYSCS_UTIL.SYSCS_EXPORT_TABLE
and SYSCS_UTIL.SYSCS_IMPORT_TABLE procedures.
The export-import worked just fine until
I realized that I cannot INSERT into any
.
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, June 15, 2006 1:05
PM
To: Derby Discussion
Subject: RE: generated by
default question
Here is a different view to the problem.
I
have a bulk export-import routine that exports all the tables into .del files
for backup using
Sorry Michael, I have to agree with Bernt and Craig. I think, for
performance reasons, it doesn't make sense to have it select max() on
the identity columns, especially since they're not guaranteed to be
indexed. I don't think you'd want Derby doing a table scan just because
you previously
-Original Message-
From: Vic Ricker [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 15, 2006 2:05 PM
To: Derby Discussion
Subject: Re: generated by default question
Sorry Michael, I have to agree with Bernt and Craig. I think, for
performance reasons, it doesn't make sense to have
Michael Segel wrote:
Uhm,
I'm sorry, but where did I ever say 'select max()'?
Hint: I didn't because its not that efficient.
Presumably he column would be indexed if you're doing operations like
max() on it anyway, so efficiency isn't the problem. I suspect the
problem would be what to do
On Thursday 15 June 2006 6:38 pm, Daniel Noll wrote:
Michael Segel wrote:
Uhm,
I'm sorry, but where did I ever say 'select max()'?
Hint: I didn't because its not that efficient.
Presumably he column would be indexed if you're doing operations like
max() on it anyway, so efficiency
Michael Segel wrote:
Presumably he column would be indexed if you're doing operations like
max() on it anyway, so efficiency isn't the problem. I suspect the
problem would be what to do when two users tried to insert rows at
around the same time. The second would fail to commit the transaction
On Thursday 15 June 2006 8:52 pm, Daniel Noll wrote:
Sigh.
If you're doing an insert on a table with an identity column, then yes I
believe that the index would be in memory. Or a memory page that was cached.
But you're missing the point.
When do you get the max() value of the index? ;-)
And
Daniel Noll wrote:
Ah. I was making the assumption that the indexes were implemented
properly. For instance, an index should keep a certain amount in
memory in order to have a reasonable chance of running quickly. So the
max and min should already be in-memory.
I take it that Derby
On Friday 16 June 2006 12:10 am, Vic Ricker wrote:
Daniel Noll wrote:
Ah. I was making the assumption that the indexes were implemented
properly. For instance, an index should keep a certain amount in
memory in order to have a reasonable chance of running quickly. So the
max and min
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
.
Then when you next insert a row using the default value, it will insert at
11.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 14, 2006 6:07 AM
To: Derby Discussion
Subject: Re: generated by default question
Let me clearify some items from
+1
Craig
On Jun 14, 2006, at 4:07 AM, Bernt M. Johnsen wrote:
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
Hi Mikey,
On Jun 12, 2006, at 10:05 PM, [EMAIL PROTECTED] wrote:
[mjs]
I believe the problem is in how you're interpreting clause 3):
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
by default question
Hi Mikey,
On Jun 12, 2006, at 10:05 PM, [EMAIL PROTECTED] wrote:
[mjs]
I believe the problem is in how you're interpreting clause 3):
3) If there exists a non-negative integer N such that SMIN =
CBV + N
* INC = SMAX and the value (CBV + N *
Hi Mikey,
On Jun 13, 2006, at 10:47 AM, [EMAIL PROTECTED] wrote:
by default question
Hi Mikey,
On Jun 12, 2006, at 10:05 PM, [EMAIL PROTECTED] wrote:
[mjs]
I believe the problem is in how you're interpreting clause 3):
3) If there exists a non-negative integer N such that SMIN =
Craig,
On a hunch, I did a quick test.
Since I have Informix up and running,
I created a table foo with two columns.
The first is a serial column, the second is a text string.
The table already had a row
id desc
1 Test 1
I then did the following statement(s):
INSERT INTO
Hi,
On Jun 13, 2006, at 11:01 AM, Craig L Russell wrote:
In fact, you could argue that if the implementation skipped
returning
a sequence value just because that value had been inserted by the
user into a column, it would be a bug.
I withdraw this argument after re-reading the words. It is
On Tuesday 13 June 2006 1:01 pm, Craig L Russell wrote:
Hi Mikey,
[SNIP]
[mjs] Hi Craig,
Errr. No.
In short, the sequence generation is outside of the transaction,
therefore
its possible to get a jump in the sequence number due to transactions
rolling back or individual inserts
On Tuesday 13 June 2006 1:19 pm, Craig L Russell wrote:
Hi,
On Jun 13, 2006, at 11:01 AM, Craig L Russell wrote:
In fact, you could argue that if the implementation skipped
returning
a sequence value just because that value had been inserted by the
user into a column, it would be a bug.
Hi G,
On Jun 13, 2006, at 12:07 PM, Michael Segel wrote:
If there exists a value N such that you can return V1 that doesn't
throw an
exception due to the unique constraint on the identity column, then
the
sequence should return that number. Or rather Derby should trap for
this and
or a feature
request. Then there would be no obligation to implement the
enhancement/feature
-Original Message-
From: Jean T. Anderson [mailto:[EMAIL PROTECTED]
Sent: Friday, June 09, 2006 10:33 AM
To: Derby Discussion
Subject: Re: generated by default question
Michael
Bernt,
I believe that if you look at the spec, you'll find that the current
implementation within Derby is indeed a bug.
Its your interpretation of 9.21 that I'm calling in to question...
See comments below...
-Mike
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of hilz
Sent: Thursday, June 08, 2006 11:28 PM
To: derby-user@db.apache.org
Subject: Re: generated by default question
Well...
A good solution would probably be if someone would look at the HSQLDB
code and see
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
hilz wrote:
Well...
A good solution would probably be if someone would look at the HSQLDB
code and see how they do it because it works like a charm over there!
Anyone doing this must adhere to the licence of HSQLDB.
Just because projects are open-source does not mean one can copy code
between
the
enhancement/feature
-Original Message-
From: Jean T. Anderson [mailto:[EMAIL PROTECTED]
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
Discussion
Subject: Re: generated by default question
hilz wrote:
Well...
A good solution would probably be if someone would look at the HSQLDB
code and see how they do it because it works like a charm over there!
Anyone doing this must adhere to the licence of HSQLDB.
Just because
Michael Segel wrote:
Right.
I don't think hilz was really suggesting piracy.
Thank you very much..
I wasn't asking anyone to copy anything.
I was just suggesting if someone would look at the HSQLDB code and see
how they do it to get an idea. That's all. Would that still be against
whatever
that should be done behind the scenes.
SO its not a bug but a design defect.
-G
-Original Message-
From: news [mailto:[EMAIL PROTECTED] 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
: 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
Michael Segel wrote (2006-05-30 13:09:19):
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.
That sir, is a bug.
And no, the SQL 2000 spec, as presented in this
.
-G -Original Message- From: news [mailto:[EMAIL PROTECTED]] 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
Bernt M. Johnsen wrote (2006-05-30 20:20:48):
I have not studied this well enough to conclude wether Derby's current
behaviour is compliant with the SQL 2000 spec or not.
But there is nothing in the Derby charter that requires Derby to be
SQL 2000 compliant, so if you're right, Derby is not
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
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
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
50 matches
Mail list logo
