[DERBY-6948] INSERT .. SELECT produces NULL for getGeneratedKeys()

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.13.1.1
• Component/s: JDBC
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 05/Jul/17 13:45
• Updated: 07/Jul/17 07:04

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

Issue Links

• is related to: DERBY-3609 Wrong functionality of auto-generated keys support