Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6948

INSERT .. SELECT produces NULL for getGeneratedKeys()

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.13.1.1
    • Fix Version/s: None
    • Component/s: JDBC
    • Labels:
      None

      Description

      The following code:

      import java.sql.Connection;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.Statement;
      
      import org.apache.derby.jdbc.EmbeddedDataSource;
      
      public class Derby {
          public static void main(String[] args) throws Exception {
              EmbeddedDataSource ds;
      
              ds = new EmbeddedDataSource();
              ds.setDatabaseName("memory:test;create=true");
      
              try (Connection con = ds.getConnection();
                   Statement s = con.createStatement()) {
      
                  s.execute(
                      "CREATE TABLE test ("
                    + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
                    + "name VARCHAR(255))");
      
                  try (PreparedStatement ps = con.prepareStatement("insert into test (name) select 'a' from sysibm.sysdummy1", Statement.RETURN_GENERATED_KEYS)) {
                      ps.executeUpdate();
      
                      try (ResultSet rs = ps.getGeneratedKeys()) {
                          while (rs.next())
                              System.out.println("GEN_ID: " + rs.getObject(1));
                      }
                  }
      
                  try (PreparedStatement ps = con.prepareStatement("insert into test (name) select 'a' from sysibm.sysdummy1", Statement.RETURN_GENERATED_KEYS)) {
                      ps.executeUpdate();
      
                      try (ResultSet rs = ps.getGeneratedKeys()) {
                          while (rs.next())
                              System.out.println("GEN_ID: " + rs.getObject(1));
                      }
                  }
      
                  try (ResultSet rs = s.executeQuery("select id from test")) {
                      while (rs.next())
                          System.out.println("    ID: " + rs.getObject(1));
                  }
              }
          }
      }
      

      Produces this output:

      GEN_ID: null
      GEN_ID: null
          ID: 1
          ID: 2
          ID: 3
      

      The expected output would be:

      GEN_ID: 1
      GEN_ID: 2
      GEN_ID: 3
          ID: 1
          ID: 2
          ID: 3
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lukas.eder Lukas Eder
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: