Bug with getGeneratedKeys() ?

[Gurvan Le Guernic <gl...@gmail.com>]

   Hi,
 I have a problem with the function "getGeneratedKeys()", it is either a 
mistake I do but do not see, or a bug.

 I have two tables created as follows:
    private static final String typesTableCreationStr =
    "CREATE TABLE types (" +
    "  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
    "  name VARCHAR(128) NOT NULL UNIQUE," +
    "  shortName VARCHAR(64) NOT NULL" +
    ")";
    private static final String methodsTableCreationStr =
    "CREATE TABLE methods (" +
    "  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
    "  class INT REFERENCES types (id)," +
    "  name VARCHAR(128) NOT NULL," +
    "  isConstructor VARCHAR(5) NOT NULL," +
    "  isStatic VARCHAR(5) NOT NULL," +
    "  returnType INT REFERENCES types (id)" +
    ")";

I want to add an entry in table "methods" and retrieve the generated key 
(it works well with a simple INSERT INTO types).
I have already 4 entries in table types with id 1, 2, 3, and 4; table 
methods is empty.
I do:
    private static final String insertStr_method =
    "INSERT INTO methods" +
    "  (class, name, isConstructor, isStatic, returnType)" +
    "  SELECT c.id, ?, ?, ?, r.id" +
    "  FROM types AS c" +
    "     , types AS r" +
    "  WHERE c.name = ?" +
    "    AND r.name = ?";
    ...
        stmt = dbConnection.prepareStatement(insertStr_method, 
Statement.RETURN_GENERATED_KEYS);
        ...
        stmt.executeUpdate();
        ResultSet res = stmt.getGeneratedKeys();
        if ( res.next() ) {
          id = res.getInt(1);
          ...
        }
Then id equals 4. I would have expected 1 as it is the first insert into 
table methods. 4 is the last generated key of table "types" but the 
insert is done into table "methods", so I would have expected to get the 
last generated key of table "methods".
 I am using db-derby-10.4.2.0 and jdk1.6.0_11 under Linux
 I attached the java files I coded.

 So, is it a bug or something I do wrong.

   Thank you,
   Gurvan

Re: Bug with getGeneratedKeys() ?

[Gurvan Le Guernic <gl...@gmail.com>]

Knut Anders Hatlen wrote:
> Gurvan Le Guernic <gl...@gmail.com> writes:
>
>   
>>   Hi,
>> I have a problem with the function "getGeneratedKeys()", it is either
>> a mistake I do but do not see, or a bug.
>>     
>
> Hi,
>
> It sounds like you may be hitting this bug:
> https://issues.apache.org/jira/browse/DERBY-3609
>
>   
Hi,
It seems you are right. Thank you for the pointer to it.

Re: Bug with getGeneratedKeys() ?

[Knut Anders Hatlen <Kn...@Sun.COM>]

Gurvan Le Guernic <gl...@gmail.com> writes:

>   Hi,
> I have a problem with the function "getGeneratedKeys()", it is either
> a mistake I do but do not see, or a bug.

Hi,

It sounds like you may be hitting this bug:
https://issues.apache.org/jira/browse/DERBY-3609

-- 
Knut Anders