[DERBY-6994] Only last generated key in batch update is returned

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.14.1.0
• Issue & fix info: Repro attached
• Assignee: Unassigned
• Reporter: Philippe Marschall
• Created: 30/Mar/18 11:34
• Updated: 03/Apr/18 23:15
• Resolved: 30/Mar/18 13:51

Description

When a batch update is executed only the generated keys of the last update are returned. Consider the following code.

    EmbeddedDataSource derbyDataSource = new EmbeddedDataSource();
    derbyDataSource.setDatabaseName("memory:test");
    derbyDataSource.setCreateDatabase("create");


    try (Connection connection = derbyDataSource.getConnection()) {

      try (Statement statement = connection.createStatement()) {
        statement.execute("CREATE TABLE test_table ("
                + "id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY,"
                + "test_value INTEGER"
                + ")");
      }

      try (PreparedStatement preparedStatement = connection.prepareStatement(
              "INSERT INTO test_table(test_value) VALUES (?)",
              Statement.RETURN_GENERATED_KEYS)) {
        preparedStatement.setObject(1, Integer.valueOf(42));
        preparedStatement.addBatch();
        preparedStatement.setObject(1, Integer.valueOf(43));
        preparedStatement.addBatch();

        int[] updateCount = preparedStatement.executeBatch();
        assertArrayEquals(new int[] {1, 1}, updateCount, "update count");

        List<Integer> generatedIntegers = new ArrayList<>(2);
        try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
          while (generatedKeys.next()) {
            generatedIntegers.add(generatedKeys.getObject(1, Integer.class));
          }
        }
        assertEquals(Arrays.asList(1, 2), generatedIntegers, "generated keys");
      }
    }

 

two updates are performed in a single batch. The first generates the key 1 and the second generates the key 2 but only the key 2 is returned.

Issue Links

• duplicates: DERBY-3609 Wrong functionality of auto-generated keys support