[jira] [Commented] (DERBY-6849) Statement.RETURN_GENERATED_KEYS returns "keys" even if there are no auto-generated fields

["Bryan Pendleton (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6849?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15076360#comment-15076360 ] 

Bryan Pendleton commented on DERBY-6849:
----------------------------------------

Some related jobs: DERBY-3609 DERBY-2631

And, here's how we currently document getGeneratedKeys:
http://db.apache.org/derby/docs/10.12/ref/rrefjdbc40794.html

  If the user has indicated that auto-generated keys should 
  be made available, this method returns the same results 
  as a call to the IDENTITY_VAL_LOCAL function. 
  Otherwise this method returns null.

And here's how we currently document IDENTITY_VAL_LOCAL:
http://db.apache.org/derby/docs/10.12/ref/rrefidentityvallocal.html#rrefidentityvallocal

  The result of the function is not affected by the following:

    A single row INSERT statement with a VALUES clause or 
    single row UPDATE statement for a table without an identity column

John, is it possible that, in your case, some **previous** statement in your
application generated a value for an identity column, and then, since
your **current** statement accesses a table without an identity column,
the value you are seeing in getGeneratedKeys is actually the generated
identity value for that **previous** statement?


> Statement.RETURN_GENERATED_KEYS returns "keys" even if there are no auto-generated fields
> -----------------------------------------------------------------------------------------
>
>                 Key: DERBY-6849
>                 URL: https://issues.apache.org/jira/browse/DERBY-6849
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.9.1.0
>            Reporter: John Hendrikx
>         Attachments: DERBY6849Repro.java
>
>
> I have a very simple table:
> {noformat}
>     CREATE TABLE images (
>       url varchar(1000) NOT NULL,
>       image blob NOT NULL,
>   
>       CONSTRAINT images_url PRIMARY KEY (url)
>     );
> {noformat}
> No auto-generated fields.  However when I do an insert, JDBC tells me there are auto-generated keys (rs.next() does not return false and a LONG value is returned):
> {noformat}
>       try(PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
>         setParameters(parameterValues, statement);
>         statement.execute();
>         try(ResultSet rs = statement.getGeneratedKeys()) {
>           if(rs.next()) {
>             return rs.getObject(1);
>           }
>           return null;
>         }
>       }
>       catch(SQLException e) {
>         throw new DatabaseException(this, sql + ": " + parameters, e);
>       }
> {noformat}
> This sounds like a bug to me.  For comparison, PostgreSQL does not have the same behaviour.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)