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

INSERT .. SELECT produces NULL for getGeneratedKeys()

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.13.1.1
    • None
    • JDBC
    • 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

              Unassigned Unassigned
              lukas.eder Lukas Eder
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: