db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6849) Statement.RETURN_GENERATED_KEYS returns a 1 row result set even if there are no auto-generated fields
Date Sun, 27 Nov 2016 16:58:58 GMT

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

Bryan Pendleton commented on DERBY-6849:

I've been thinking about how to address the "where to release the generated keys" problem
raised by Knut Anders's patch. To review, here's the challenge posed by the patch:

+        // The InsertResultSet will be closed before Statement.execute()
+        // returns, so closing the holder for the generated keys here means
+        // it won't be available when Statement.getGeneratedKeys() is called.
+        // For now, just as a hack to get it working, comment it out.
+//		if (autoGeneratedKeysRowsHolder != null) {
+//			autoGeneratedKeysRowsHolder.close();
+//		}

The issue, as I see it, is that the responsibility for tracking, and for eventually
releasing, the information about the generated keys is spread across four
different classes:
- BaseActivation knows whether we are tracking generated keys, and which particular ones:
	//Following three are used for JDBC3.0 auto-generated keys feature.
	//autoGeneratedKeysResultSetMode will be set true if at the time of statement execution,
	//either Statement.RETURN_GENERATED_KEYS was passed or an array of (column positions or
	//column names) was passed
	private boolean autoGeneratedKeysResultSetMode;
	private int[] autoGeneratedKeysColumnIndexes ;
	private String[] autoGeneratedKeysColumnNames ;
- EmbedStatement contains the result set which can be used to retrieve the generated keys:
	//for jdbc3.0 feature, where you can get a resultset of rows inserted
	//for auto generated columns after an insert
	private java.sql.ResultSet autoGeneratedKeysResultSet;
- DMLWriteGeneratedColumnsResultSet records the actual generated keys themselves:
	//following is for jdbc3.0 feature auto generated keys resultset
	protected  ResultSet			autoGeneratedKeysResultSet;
	protected  TemporaryRowHolderImpl	autoGeneratedKeysRowsHolder;
	protected  int[]                   autoGeneratedKeysColumnIndexes;
- and InsertResultSet, which subclasses DMLWriteGeneratedColumnsResultSet, releases that data:
	public void close() throws StandardException {
		close( constants.underMerge() );
		if (autoGeneratedKeysRowsHolder != null) {

Moreover, this part of the code is quite tricky because it is built atop a much
older feature (IDENTITY_VAL_LOCAL) which tracked the AUTOINCREMENT
column feature and supports very old databases which implemented this
feature prior to the introduction of the SEQUENCE object into Derby.

So changing any of this stuff is quite delicate.

But it does seem to me that the information about the keys that were generated
is (at least partly) in the wrong place: it belongs with the statement, not with
the result set, because its lifetime should match the statements lifetime, not the
InsertResultSet's lifetime.

So perhaps an incremental first step is to accomplish such a refactoring?

> Statement.RETURN_GENERATED_KEYS returns a 1 row result set even if there are no auto-generated
> -----------------------------------------------------------------------------------------------------
>                 Key: DERBY-6849
>                 URL: https://issues.apache.org/jira/browse/DERBY-6849
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions:
>            Reporter: John Hendrikx
>         Attachments: DERBY6849Repro.java, firstExperimentNotForCommit.diff, generated-keys-with-tests.diff
> If:
> 1) A JDBC INSERT statement is executed, with Statement.RETURN_GENERATED_KEYS enabled,
> 2) A call is then made to Statement.getGeneratedKeys, and
> 3) The table which was inserted into has *NO* generated columns,
> then getGeneratedKeys() returns a ResultSet object with a single row in it.
> This behavior seems incorrect; it seems that the correct behavior
> would be to return a ResultSet object which has *NO* rows in it, so
> that ResultSet.next() returns FALSE the first time it is called.
> 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

View raw message