Derby
  1. Derby
  2. DERBY-4015

Regression in Statement.getGeneratedKeys()

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Critical Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.3.3.0
    • Fix Version/s: None
    • Component/s: JDBC, Network Client
    • Environment:
      I am currently running on Mac 10.5 with Java 5. However, it has also been observed on other platforms in our automated build such as SUSE Linux and Windows with Java 5.
    • Urgency:
      Normal
    • Issue & fix info:
      Release Note Needed
    • Bug behavior facts:
      Deviation from standard, Embedded/Client difference, Regression

      Description

      The application of both DERBY-2653 and DERBY-3426 in the 10.3.3.0 seems to have created a regression in how the network JDBC client implementation of PreparedStatement.getGeneratedKeys() behaves as compared to 10.3.2.1. Note that there wasn't a change in behavior between versions for the embedded implementation. However, in general there are behavior discrepancies between the network and client implementations for this method.

      1. Behavior-Matrix.jpg
        53 kB
        Brandon Smith
      2. derby-10.3.2.1-tests.zip
        11 kB
        Brandon Smith
      3. derby-10.3.3.0-tests.zip
        256 kB
        Brandon Smith
      4. Derby4015.java
        4 kB
        Kathey Marsden
      5. updatedBehaviorMatrix.txt
        1 kB
        Kathey Marsden
      6. Derby4015.java
        4 kB
        Kathey Marsden
      7. Derby4015.java
        6 kB
        Rick Hillegas
      8. updatedBehaviorMatrix.txt
        2 kB
        Kathey Marsden
      9. Derby4015.java
        7 kB
        Kathey Marsden
      10. Derby4015.java
        7 kB
        Knut Anders Hatlen
      11. updatedBehaviorMatrix.txt
        2 kB
        Kathey Marsden
      12. updatedBehaviorMatrix.txt
        4 kB
        Kathey Marsden
      13. Derby4015.java
        9 kB
        Kathey Marsden

        Issue Links

          Activity

          Hide
          Brandon Smith added a comment -

          Consider the attached screenshot illustrating a behavior matrix between client and embedded, versions 10.3.2.1 and 10.3.3.0, and four test cases.

          The test cases each call Connection.prepareStatement(String, String[]) with different values.

          emptyArray == new String[] { }
          fakeColumn == new String[]

          { "value" }

          where value is not a column
          nullArray == new String[]

          { null }

          nullParam == null

          The matrix then records how each behaves and illustrates how the behavior changes not only across versions, but also between client and embedded implementations.

          Show
          Brandon Smith added a comment - Consider the attached screenshot illustrating a behavior matrix between client and embedded, versions 10.3.2.1 and 10.3.3.0, and four test cases. The test cases each call Connection.prepareStatement(String, String[]) with different values. emptyArray == new String[] { } fakeColumn == new String[] { "value" } where value is not a column nullArray == new String[] { null } nullParam == null The matrix then records how each behaves and illustrates how the behavior changes not only across versions, but also between client and embedded implementations.
          Hide
          Brandon Smith added a comment - - edited

          Attaching derby-10.3.2.1-tests.zip Eclipse project with unit tests make JUnit assertions that illustrate the network and embedded behavior for 10.3.2.1 PreparedStatement.getGeneratedKeys()

          It will be required to modify the Eclipse project build path to point to Derby 10.3.2.1 jars.

          Show
          Brandon Smith added a comment - - edited Attaching derby-10.3.2.1-tests.zip Eclipse project with unit tests make JUnit assertions that illustrate the network and embedded behavior for 10.3.2.1 PreparedStatement.getGeneratedKeys() It will be required to modify the Eclipse project build path to point to Derby 10.3.2.1 jars.
          Hide
          Brandon Smith added a comment - - edited

          Likewise, I am Attaching derby-10.3.3.0-tests.zip Eclipse project with unit tests make JUnit assertions that illustrate the network and embedded behavior for 10.3.3.0 PreparedStatement.getGeneratedKeys()

          It will be required to modify the Eclipse project build path to point to Derby 10.3.3.0 jars.

          Show
          Brandon Smith added a comment - - edited Likewise, I am Attaching derby-10.3.3.0-tests.zip Eclipse project with unit tests make JUnit assertions that illustrate the network and embedded behavior for 10.3.3.0 PreparedStatement.getGeneratedKeys() It will be required to modify the Eclipse project build path to point to Derby 10.3.3.0 jars.
          Hide
          Kristian Waagan added a comment -

          Seems to me that there are several issues to consider here (I haven't checked the current status):
          o Ignore array/argument if the SQL is not an insert statement (or not another type of statement returning a generated key).
          o In cases where there are no generated keys to return, an empty result set should be returned (not null).
          o If Derby doesn't support the functionality, SQLFeatureNotSupportedException should be thrown.
          o Make embedded and client driver consistent.

          It is not quite clear to me what to do if the column index or column name is invalid, but it seems wise to thrown an SQLExcecption.
          A few questions:
          a) Is a generated key column always an integer column?
          b) Can a generated key column be any generated column, or must it be GENERATED

          { ALWAYS | BY DEFAULT }

          AS IDENTITY?
          c) Derby supports only one identity column. Does this mean that if two or more column names/indexes are specified, an SQLFeatureNotSupportedException should be thrown?

          Show
          Kristian Waagan added a comment - Seems to me that there are several issues to consider here (I haven't checked the current status): o Ignore array/argument if the SQL is not an insert statement (or not another type of statement returning a generated key). o In cases where there are no generated keys to return, an empty result set should be returned (not null). o If Derby doesn't support the functionality, SQLFeatureNotSupportedException should be thrown. o Make embedded and client driver consistent. It is not quite clear to me what to do if the column index or column name is invalid, but it seems wise to thrown an SQLExcecption. A few questions: a) Is a generated key column always an integer column? b) Can a generated key column be any generated column, or must it be GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY? c) Derby supports only one identity column. Does this mean that if two or more column names/indexes are specified, an SQLFeatureNotSupportedException should be thrown?
          Hide
          Kathey Marsden added a comment -

          I created a small java program from Brandon's tests so we could remove the eclipse dependency since not everyone uses eclipse. To run embedded run:
          java Derby4015.

          For embedded on trunk the result is:

          emptyArray test (new String[] { })
          Null Result

          fakeColumn test: (new String[]

          { "value" })
          Caught exception SQLSTATE=X0X0F message=Table 'INSERTTEST' does not have an auto-generated column named 'value'.

          nullArray test: (new String[] { null })
          Caught exception SQLSTATE=X0X0F message=Table 'INSERTTEST' does not have an auto-generated column named 'null'.

          nullParamTest (null)
          Null Result

          To run for client, start network server and run
          java Derby4015 client
          For client the results are:

          emptyArray test (new String[] { })
          Null Result

          fakeColumn test: (new String[] { "value" }

          )
          2

          nullArray test: (new String[]

          { null }

          )
          3

          nullParam test (null)
          Null Result

          One thing I notice is that for the fakeColumn and nullArray cases the matrix is not correct. For the fakeColumn and nullArray cases we do not return an empty result for client; we return the value of the identity column. This is documented as the current client behavior:
          http://db.apache.org/derby/docs/10.4/ref/crefjavstateautogen.html

          "With the client driver, the one element column name is ignored currently and the value returned corresponds to the identity column. To ensure compatibility with future changes an application should ensure the column described is the identity column. If the column name corresponds to another column or a non-existent column then future changes may result in a value for a different column being returned or an exception being thrown."

          The client currently has no way to know which is the generated column so can't check.

          A couple things in the javadoc struck me.
          First is this note:
          Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys.

          Does this mean that for the emptyArray and nullParam cases we should return the generated keys?

          Secondly for SQLException it says
          SQLException - if a database access error occurs or this method is called on a closed Statement

          It seems to limit SQLExceptions to these cases so it is not clear what should be done if the column names specified do not match the identity columns.

          It would be good to get spec clarification on how these cases should behave.

          Show
          Kathey Marsden added a comment - I created a small java program from Brandon's tests so we could remove the eclipse dependency since not everyone uses eclipse. To run embedded run: java Derby4015. For embedded on trunk the result is: emptyArray test (new String[] { }) Null Result fakeColumn test: (new String[] { "value" }) Caught exception SQLSTATE=X0X0F message=Table 'INSERTTEST' does not have an auto-generated column named 'value'. nullArray test: (new String[] { null }) Caught exception SQLSTATE=X0X0F message=Table 'INSERTTEST' does not have an auto-generated column named 'null'. nullParamTest (null) Null Result To run for client, start network server and run java Derby4015 client For client the results are: emptyArray test (new String[] { }) Null Result fakeColumn test: (new String[] { "value" } ) 2 nullArray test: (new String[] { null } ) 3 nullParam test (null) Null Result One thing I notice is that for the fakeColumn and nullArray cases the matrix is not correct. For the fakeColumn and nullArray cases we do not return an empty result for client; we return the value of the identity column. This is documented as the current client behavior: http://db.apache.org/derby/docs/10.4/ref/crefjavstateautogen.html "With the client driver, the one element column name is ignored currently and the value returned corresponds to the identity column. To ensure compatibility with future changes an application should ensure the column described is the identity column. If the column name corresponds to another column or a non-existent column then future changes may result in a value for a different column being returned or an exception being thrown." The client currently has no way to know which is the generated column so can't check. A couple things in the javadoc struck me. First is this note: Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys. Does this mean that for the emptyArray and nullParam cases we should return the generated keys? Secondly for SQLException it says SQLException - if a database access error occurs or this method is called on a closed Statement It seems to limit SQLExceptions to these cases so it is not clear what should be done if the column names specified do not match the identity columns. It would be good to get spec clarification on how these cases should behave.
          Hide
          Kristian Waagan added a comment -

          Kathey wrote:


          A couple things in the javadoc struck me.
          First is this note:
          Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys.

          Does this mean that for the emptyArray and nullParam cases we should return the generated keys?


          When I read this, I thought of the methods where you specify that you want the generated keys, but not which. One such example is "prepareStatement(String sql, int autoGeneratedKeys) ".

          It seems wrong to me to return the keys for emptyArray and nullParam, but I agree it isn't very clear from reading the spec. I also checked the JDBC 3.0 Tutorial book quickly (feel free to have a second look!), but didn't find anything helpful. If you want everything, shouldn't you either specify them all or use the method I mentioned above?
          Also, could it be that Derby's implementation takes a few shortcuts because we only allow one identity column per table?

          I would let emptyArray return an empty result set and maybe have nullParam throw an exception, but again we have several choices...

          Show
          Kristian Waagan added a comment - Kathey wrote: A couple things in the javadoc struck me. First is this note: Note:If the columns which represent the auto-generated keys were not specified, the JDBC driver implementation will determine the columns which best represent the auto-generated keys. Does this mean that for the emptyArray and nullParam cases we should return the generated keys? When I read this, I thought of the methods where you specify that you want the generated keys, but not which. One such example is "prepareStatement(String sql, int autoGeneratedKeys) ". It seems wrong to me to return the keys for emptyArray and nullParam, but I agree it isn't very clear from reading the spec. I also checked the JDBC 3.0 Tutorial book quickly (feel free to have a second look!), but didn't find anything helpful. If you want everything, shouldn't you either specify them all or use the method I mentioned above? Also, could it be that Derby's implementation takes a few shortcuts because we only allow one identity column per table? I would let emptyArray return an empty result set and maybe have nullParam throw an exception, but again we have several choices...
          Hide
          Kathey Marsden added a comment -

          In the javadoc for the executeUpdate
          http://java.sun.com/javase/6/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20java.lang.String[])

          SQLException - if a database access error occurs, this method is called on a closed Statement, the SQL statement returns a ResultSet object, or the second argument supplied to this method is not a String array whose elements are valid column names

          So it looks like the exception should occur on the executeUpdate(), not getGeneratedKeys() for the emptyArray, nullArray, and nullParam cases anyway.

          For the fakeColumn case, the column supplied is actually a valid column (albeit not an identity column), so perhaps it should return an empty result set? For a non-existent column it should also throw an exception on executeUpdate too. Of course we currently can't detect this in the client driver but it would be good to decide the correct behavior and then worry about implementation later.

          Show
          Kathey Marsden added a comment - In the javadoc for the executeUpdate http://java.sun.com/javase/6/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String,%20java.lang.String[ ]) SQLException - if a database access error occurs, this method is called on a closed Statement, the SQL statement returns a ResultSet object, or the second argument supplied to this method is not a String array whose elements are valid column names So it looks like the exception should occur on the executeUpdate(), not getGeneratedKeys() for the emptyArray, nullArray, and nullParam cases anyway. For the fakeColumn case, the column supplied is actually a valid column (albeit not an identity column), so perhaps it should return an empty result set? For a non-existent column it should also throw an exception on executeUpdate too. Of course we currently can't detect this in the client driver but it would be good to decide the correct behavior and then worry about implementation later.
          Hide
          Kristian Waagan added a comment -

          Yes, I too understand that the exception should be thrown when calling the various executeX and prepareX methods.

          Show
          Kristian Waagan added a comment - Yes, I too understand that the exception should be thrown when calling the various executeX and prepareX methods.
          Hide
          Kathey Marsden added a comment -

          I decided to look at the JCC/DB2 behavior. With JCC 3.5 I get the following output for the Derby4015 program.

          emptyArray test (new String[] { })
          1

          fakeColumn test: (new String[]

          { "value" }

          )
          100

          nullArray test: (new String[]

          { null }

          )
          Caught exception SQLSTATE=42703 message=DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=NULL, DRIVER=3.57.38
          (An undefined column, attribute, or parameter name was detected.)

          nullParam test (null)
          3

          So for emptyArray and nullParam it actually returns the generated value. For the non-generated column it returns the non-generated value inserted there which is interesting. If I pass a column name that does not exist for the fakeColumn test I get the 42703 exception.

          It would be interesting to know what other databases do.

          Show
          Kathey Marsden added a comment - I decided to look at the JCC/DB2 behavior. With JCC 3.5 I get the following output for the Derby4015 program. emptyArray test (new String[] { }) 1 fakeColumn test: (new String[] { "value" } ) 100 nullArray test: (new String[] { null } ) Caught exception SQLSTATE=42703 message=DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=NULL, DRIVER=3.57.38 (An undefined column, attribute, or parameter name was detected.) nullParam test (null) 3 So for emptyArray and nullParam it actually returns the generated value. For the non-generated column it returns the non-generated value inserted there which is interesting. If I pass a column name that does not exist for the fakeColumn test I get the 42703 exception. It would be interesting to know what other databases do.
          Hide
          Kathey Marsden added a comment -

          Attached is an updated matrix. It includes JCC->DB2 and also makes some corrections from the original matrix where we do not get an empty ResultSet but rather get the generated key. It also adds the case where we specify a non-generated column.

          Based on this I propose we make the following changes to the network driver to remove any perceived regression in 10.3:

          Change the emptyArray case to return the generated key. This will match embedded and can be interpreted as a case where the columns are not specified.

          Change the nullParam case to return the generated key. This will match the network client 10.3.2.1 behavior again with the logic being that it is simply a case where the columns are not specified so the key should be returned.

          These changes would be backported to 10.4/10.3 and then we would file a separate issue for the remaining client/embedded differences which can be addressed in the trunk.

          Sorry I keep switching back and forth on this, but I think I have at least settled my mind on a sensible solution for 10.3. Let me know what you think.

          Show
          Kathey Marsden added a comment - Attached is an updated matrix. It includes JCC->DB2 and also makes some corrections from the original matrix where we do not get an empty ResultSet but rather get the generated key. It also adds the case where we specify a non-generated column. Based on this I propose we make the following changes to the network driver to remove any perceived regression in 10.3: Change the emptyArray case to return the generated key. This will match embedded and can be interpreted as a case where the columns are not specified. Change the nullParam case to return the generated key. This will match the network client 10.3.2.1 behavior again with the logic being that it is simply a case where the columns are not specified so the key should be returned. These changes would be backported to 10.4/10.3 and then we would file a separate issue for the remaining client/embedded differences which can be addressed in the trunk. Sorry I keep switching back and forth on this, but I think I have at least settled my mind on a sensible solution for 10.3. Let me know what you think.
          Hide
          Kathey Marsden added a comment -

          DERBY-3430 is another related issue. The emptyArray case for embedded was changed to return null.

          Show
          Kathey Marsden added a comment - DERBY-3430 is another related issue. The emptyArray case for embedded was changed to return null.
          Hide
          Kathey Marsden added a comment -

          Attached is an updated program that separates the non-generated and fake column tests. Would someone who has access be willing to run this on Postgress and/or MySQL?

          Myrna suggested that I post the trunk plan too, based on my earlier proposal. I think that the following behavior would be the ultimate goal for both client and embedded:

          emptyArray : gen key RS
          nonGen column: SQLE(X0XOF)
          fakeColumn : SQLE(XOXOF)
          nullArray : SQLE(X0XOF)
          nullParam : gen key RS

          I am not sure how to implement the SQLExceptions for the nonGen and fakeColumn cases for client though.

          Show
          Kathey Marsden added a comment - Attached is an updated program that separates the non-generated and fake column tests. Would someone who has access be willing to run this on Postgress and/or MySQL? Myrna suggested that I post the trunk plan too, based on my earlier proposal. I think that the following behavior would be the ultimate goal for both client and embedded: emptyArray : gen key RS nonGen column: SQLE(X0XOF) fakeColumn : SQLE(XOXOF) nullArray : SQLE(X0XOF) nullParam : gen key RS I am not sure how to implement the SQLExceptions for the nonGen and fakeColumn cases for client though.
          Hide
          Rick Hillegas added a comment -

          Attaching a new version of Derby4015.java. This version includes support for running the experiment on MySQL. Here are my results when I run this test against MySQL 5.0.7:

          MySQL-AB JDBC Driver
          mysql-connector-java-5.0.7 ( $Date: 2007-03-09 22:13:57 +0100 (Fri, 09 Mar 2007) $, $Revision: 6341 $ )

          emptyArray test (new String[] { })
          1

          nonGeneratedColumn test: (new String[]

          { "value" }

          )
          2

          fakeColumn test: (new String[]

          { "notthere" }

          )
          3

          nullArray test: (new String[]

          { null }

          )
          4

          nullParam test (null)
          5

          Show
          Rick Hillegas added a comment - Attaching a new version of Derby4015.java. This version includes support for running the experiment on MySQL. Here are my results when I run this test against MySQL 5.0.7: MySQL-AB JDBC Driver mysql-connector-java-5.0.7 ( $Date: 2007-03-09 22:13:57 +0100 (Fri, 09 Mar 2007) $, $Revision: 6341 $ ) emptyArray test (new String[] { }) 1 nonGeneratedColumn test: (new String[] { "value" } ) 2 fakeColumn test: (new String[] { "notthere" } ) 3 nullArray test: (new String[] { null } ) 4 nullParam test (null) 5
          Hide
          Kathey Marsden added a comment -

          Updating the matrix to include embedded 10.4/10.5 and MySQL. Thanks Rick for posting the MySQL results. MySQL interestingly returns the generated key for all cases. This at least adds some credence to returning the generated key for the emptyArray and nullParam cases, so I think I'll go ahead and pursue doing that i there is no objection. My current plan is:

          • Under this issue change network client emptyArray and nullParam classes to return the generated key.
          • File a separate issue to change embedded to return generated key for emptyArray and nullParam cases.
          • File another issue for the remaining differences between embedded and client, but not do anything about it right away.

          Sound ok?

          Show
          Kathey Marsden added a comment - Updating the matrix to include embedded 10.4/10.5 and MySQL. Thanks Rick for posting the MySQL results. MySQL interestingly returns the generated key for all cases. This at least adds some credence to returning the generated key for the emptyArray and nullParam cases, so I think I'll go ahead and pursue doing that i there is no objection. My current plan is: Under this issue change network client emptyArray and nullParam classes to return the generated key. File a separate issue to change embedded to return generated key for emptyArray and nullParam cases. File another issue for the remaining differences between embedded and client, but not do anything about it right away. Sound ok?
          Hide
          Kristian Waagan added a comment -

          I have to say I still think the results look a bit strange, but I have nothing to back up my opinion with.
          If you can specify nothing and anything, and still get a generated key returned, what is the point with the method?

          Also, are there any databases that support getting multiple columns?
          Is it possible to define multiple generated columns in MySQL and see if only the first column is returned, or all?
          How is the ordering of the columns determined?
          And why return a non-generated value?

          I agree that we should make the embedded and the client driver consistent right away. On a longer term though, I'd love to get some more information on this.

          Show
          Kristian Waagan added a comment - I have to say I still think the results look a bit strange, but I have nothing to back up my opinion with. If you can specify nothing and anything, and still get a generated key returned, what is the point with the method? Also, are there any databases that support getting multiple columns? Is it possible to define multiple generated columns in MySQL and see if only the first column is returned, or all? How is the ordering of the columns determined? And why return a non-generated value? I agree that we should make the embedded and the client driver consistent right away. On a longer term though, I'd love to get some more information on this.
          Hide
          Kathey Marsden added a comment -

          I'm all for getting more information and doing this right. What additional information do you think we should gather? Is Lance still working on the standard? Might he be able to shed some light?

          Show
          Kathey Marsden added a comment - I'm all for getting more information and doing this right. What additional information do you think we should gather? Is Lance still working on the standard? Might he be able to shed some light?
          Hide
          Kathey Marsden added a comment -

          I haven't tried it and don't have access to mySQL, but from what I can glean from a google search MySQL doesn't allow multiple AUTO_INCREMENT columns.

          Cloudscape 5.1 was able to return multiple columns. It returned generated keys for all DEFAULT values, not just autoincrement. I am not sure how it determined the order. Here is the doc.
          http://publibfi.boulder.ibm.com/epubs/html/cloud51/doc/html/coredocs/jdbc32.htm#1029345

          BTW. Cloudscape 5.1 threw exceptions for all cases: Below is the output. I am not sure if this is worth adding to the matrix since Cloudscape 5.1 is not really used anymore.

          emptyArray test (new String[] { })
          Caught exception SQLSTATE=S0022 message=Column '1' not found.

          nonGeneratedColumn test: (new String[]

          { "value" }

          )
          Caught exception SQLSTATE=X0X0F message=Column name 'value' listed in auto-generated column selection array not found in
          the insert table.

          fakeColumn test: (new String[]

          { "notthere" }

          )
          Caught exception SQLSTATE=X0X0F message=Column name 'notthere' listed in auto-generated column selection array not found
          in the insert table.

          nullArray test: (new String[]

          { null }

          )
          Caught exception SQLSTATE=X0X0F message=Column name 'null' listed in auto-generated column selection array not found in
          the insert table.

          nullParam test (null)
          Caught exception SQLSTATE=X0X08 message=The auto-generated keys resultset not generated. Possible reasons are auto-gener
          ated was requested for non-insert statement or Statement.RETURN_GENERATED_KEYS was not requested for the statement.

          Show
          Kathey Marsden added a comment - I haven't tried it and don't have access to mySQL, but from what I can glean from a google search MySQL doesn't allow multiple AUTO_INCREMENT columns. Cloudscape 5.1 was able to return multiple columns. It returned generated keys for all DEFAULT values, not just autoincrement. I am not sure how it determined the order. Here is the doc. http://publibfi.boulder.ibm.com/epubs/html/cloud51/doc/html/coredocs/jdbc32.htm#1029345 BTW. Cloudscape 5.1 threw exceptions for all cases: Below is the output. I am not sure if this is worth adding to the matrix since Cloudscape 5.1 is not really used anymore. emptyArray test (new String[] { }) Caught exception SQLSTATE=S0022 message=Column '1' not found. nonGeneratedColumn test: (new String[] { "value" } ) Caught exception SQLSTATE=X0X0F message=Column name 'value' listed in auto-generated column selection array not found in the insert table. fakeColumn test: (new String[] { "notthere" } ) Caught exception SQLSTATE=X0X0F message=Column name 'notthere' listed in auto-generated column selection array not found in the insert table. nullArray test: (new String[] { null } ) Caught exception SQLSTATE=X0X0F message=Column name 'null' listed in auto-generated column selection array not found in the insert table. nullParam test (null) Caught exception SQLSTATE=X0X08 message=The auto-generated keys resultset not generated. Possible reasons are auto-gener ated was requested for non-insert statement or Statement.RETURN_GENERATED_KEYS was not requested for the statement.
          Hide
          Kathey Marsden added a comment -

          Derby4015 with cs51 (Cloudscape 5.1) option.

          Show
          Kathey Marsden added a comment - Derby4015 with cs51 (Cloudscape 5.1) option.
          Hide
          Knut Anders Hatlen added a comment -

          Attaching new version of Derby4015.java with support for PostgreSQL.

          PostgreSQL Native Driver
          PostgreSQL 8.3 JDBC4 with SSL (build 603)

          emptyArray test (new String[] { })
          Empty Result

          nonGeneratedColumn test: (new String[]

          { "value" }

          )
          Caught exception SQLSTATE=0A000 message=Returning autogenerated keys is not supported.

          fakeColumn test: (new String[]

          { "notthere" }

          )
          Caught exception SQLSTATE=0A000 message=Returning autogenerated keys is not supported.

          nullArray test: (new String[]

          { null }

          )
          Caught exception SQLSTATE=0A000 message=Returning autogenerated keys is not supported.

          nullParam test (null)
          Exception in thread "main" java.lang.NullPointerException
          at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:447)
          at Derby4015.runUntilGeneratedKeysCall(Derby4015.java:116)
          at Derby4015.nullParam(Derby4015.java:189)
          at Derby4015.main(Derby4015.java:81)

          Show
          Knut Anders Hatlen added a comment - Attaching new version of Derby4015.java with support for PostgreSQL. PostgreSQL Native Driver PostgreSQL 8.3 JDBC4 with SSL (build 603) emptyArray test (new String[] { }) Empty Result nonGeneratedColumn test: (new String[] { "value" } ) Caught exception SQLSTATE=0A000 message=Returning autogenerated keys is not supported. fakeColumn test: (new String[] { "notthere" } ) Caught exception SQLSTATE=0A000 message=Returning autogenerated keys is not supported. nullArray test: (new String[] { null } ) Caught exception SQLSTATE=0A000 message=Returning autogenerated keys is not supported. nullParam test (null) Exception in thread "main" java.lang.NullPointerException at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:447) at Derby4015.runUntilGeneratedKeysCall(Derby4015.java:116) at Derby4015.nullParam(Derby4015.java:189) at Derby4015.main(Derby4015.java:81)
          Hide
          Kathey Marsden added a comment -

          Updating the matrix with PostgreSQL behavior.

          Show
          Kathey Marsden added a comment - Updating the matrix with PostgreSQL behavior.
          Hide
          Kathey Marsden added a comment -

          It would be good also to get spec clarification for the following cases:
          NO_GENERATED_KEYS specified. Should getGeneratedKeys() return null or empty ResultSet?

          RETURN_GENERATED_KEYS specified but none exists
          Currently Derby returns a 1 row ResultSet with a value of null. I think this is wrong. I think it should be an empty ResultSet.

          Show
          Kathey Marsden added a comment - It would be good also to get spec clarification for the following cases: NO_GENERATED_KEYS specified. Should getGeneratedKeys() return null or empty ResultSet? RETURN_GENERATED_KEYS specified but none exists Currently Derby returns a 1 row ResultSet with a value of null. I think this is wrong. I think it should be an empty ResultSet.
          Hide
          Kathey Marsden added a comment -

          Adding updated Derby4015.java with the two other cases I mentioned and updated matrix with the results for those cases for Derby and DB2.

          Show
          Kathey Marsden added a comment - Adding updated Derby4015.java with the two other cases I mentioned and updated matrix with the results for those cases for Derby and DB2.
          Hide
          Kathey Marsden added a comment -

          I think the noGenKeysSpecified (Call prepareStatement(String sql,int autogeneratedKeys) with an insert that generates a key and pass Statement.NO_GENERATED_KEYS for the autogeneratedKeys value)

          is the key (no pun intended) to resolving this issue. I think DB2 is right to return an empty ResultSet in this case and we are wrong to return null. The javadoc says:

          If this Statement object did not generate any keys, an empty ResultSet object is returned.

          so to me, that indicates we should have an empty RS not null for this case.

          My current proposal is this: Open a new issue:

          If NO_GENERATED_KEYS is specified, Statement.getGeneratedKeys should return an empty ResultSet, not null

          Since we treat emptyArray and nullParam as NO_GENERATED_KEYS they would also change to return an empty ResultSet. So we would have the following changes.
          emptyArray empty RS
          nullParam empty RS
          noGenKeysSpecified empty RS

          I'd backport both the client and embedded changes to 10.3 which is a bit risky but awfully messy otherwise. This issue can then be duped to the new bug.

          Finally we can open up another issue to resolve the other cases.

          Thoughts?

          Show
          Kathey Marsden added a comment - I think the noGenKeysSpecified (Call prepareStatement(String sql,int autogeneratedKeys) with an insert that generates a key and pass Statement.NO_GENERATED_KEYS for the autogeneratedKeys value) is the key (no pun intended) to resolving this issue. I think DB2 is right to return an empty ResultSet in this case and we are wrong to return null. The javadoc says: If this Statement object did not generate any keys, an empty ResultSet object is returned. so to me, that indicates we should have an empty RS not null for this case. My current proposal is this: Open a new issue: If NO_GENERATED_KEYS is specified, Statement.getGeneratedKeys should return an empty ResultSet, not null Since we treat emptyArray and nullParam as NO_GENERATED_KEYS they would also change to return an empty ResultSet. So we would have the following changes. emptyArray empty RS nullParam empty RS noGenKeysSpecified empty RS I'd backport both the client and embedded changes to 10.3 which is a bit risky but awfully messy otherwise. This issue can then be duped to the new bug. Finally we can open up another issue to resolve the other cases. Thoughts?
          Hide
          Kristian Waagan added a comment -

          I agree we should never return null from getGeneratedKeys, and handling the different cases under more than one issue sounds like a good idea to me.

          Show
          Kristian Waagan added a comment - I agree we should never return null from getGeneratedKeys, and handling the different cases under more than one issue sounds like a good idea to me.
          Hide
          Knut Anders Hatlen added a comment -

          Triaged for 10.5.2. Added component JDBC. Leaving urgency at Critical since it is a regression.

          Kathey, it was a bit unclear to me what you actually suggested that we do with this issue (see comment dated 23/Jan/09). Did you suggest that we close this as a duplicate of DERBY-4031 now? Or did you suggest that we wait until DERBY-4031 has been fixed and back-ported?

          Show
          Knut Anders Hatlen added a comment - Triaged for 10.5.2. Added component JDBC. Leaving urgency at Critical since it is a regression. Kathey, it was a bit unclear to me what you actually suggested that we do with this issue (see comment dated 23/Jan/09). Did you suggest that we close this as a duplicate of DERBY-4031 now? Or did you suggest that we wait until DERBY-4031 has been fixed and back-ported?
          Hide
          Kathey Marsden added a comment -

          Last I heard Lance and the E.G. were still discussing the correct behavior for the various cases. We should follow up with Lance and see if he can send status to the list.

          Show
          Kathey Marsden added a comment - Last I heard Lance and the E.G. were still discussing the correct behavior for the various cases. We should follow up with Lance and see if he can send status to the list.
          Hide
          Kathey Marsden added a comment -

          not working on this now

          Show
          Kathey Marsden added a comment - not working on this now

            People

            • Assignee:
              Unassigned
              Reporter:
              Brandon Smith
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development