db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Justin Patterson <jus...@pattersonhouse.net>
Subject Expected behavior of identity_val_local()?
Date Thu, 02 Feb 2006 19:16:07 GMT
I'm working with Hibernate 3 and Derby 10.1.2.1 and some bad things 
started happening (I just ported to Derby from One$DB).  I traced it 
back to a behavior in Derby and I'm wondering if it's the expected 
behavior or not.  I've simplified the condition below.

CREATE TABLE test1 (
  id INTEGER GENERATED ALWAYS AS IDENTITY
);

INSERT INTO test1 (id) VALUES (default);
VALUES identity_val_local();

DROP TABLE test1;

This mimics what Hibernate is doing in my application.  I would expect 
that identity_val_local() would return '1' here, but instead it's set to 
NULL.  It seems to be related to the fact that there's only one column 
and it's the identity column.  The reason that I say this is because, if 
I create a table with another column, and insert a record into it with 
the 'id' defaulted either explicitly or implicitly (through omission), 
it works as I would expect.

CREATE TABLE test2 (
  id INTEGER GENERATED ALWAYS AS IDENTITY,
  dummy SMALLINT
);

INSERT INTO test2 (id,dummy) VALUES (default,8);
VALUES identity_val_local();
INSERT INTO test2 (dummy) VALUES (8);
VALUES identity_val_local();

DROP TABLE test2;

In this case it returns first '1' and then '2'.

I saw that there were some conditions under which the value returned by 
identity_val_local() was not affected, but I don't think that a table 
with only one column that's the identity column was one of those cases.

Any advice is greatly appreciated.

Thanks,
-Justin


Mime
View raw message