[BUG] DatabaseMetaData#getImportedKeys() returns duplicate rows if multiple FKs have the same name

[joel-ws]

Driver version

7.2.2

SQL Server version

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)

Client Operating System

Windows 10 Enterprise 1803

JAVA/JVM version

11.0.3+7 AdoptOpenJDK Hotspot

Table schema

CREATE TABLE [s1].[PkTable1] (
    [col] INT PRIMARY KEY
);

CREATE TABLE [s1].[FkTable1] (
    [col] int,
    CONSTRAINT fk_DuplicateName FOREIGN KEY ([col]) REFERENCES [s1].[PkTable1]([col])
);

CREATE TABLE [s2].[PkTable2] (
    [col] INT PRIMARY KEY
);

CREATE TABLE [s2].[FkTable2] (
    [col] INT,
    CONSTRAINT fk_DuplicateName FOREIGN KEY ([col]) REFERENCES [s2].[PkTable2]([col])
);

Problem description

When foreign keys are defined with the same names but in different schemas DatabaseMetaData#getImportedKeys() returns a result set with duplicate rows.

Expected behaviour: the result set contains one row per foreign key column
Actual behaviour: the result set contains multiple duplicate rows per foreign key column

This appears to be caused by the join at the end of the query run by SQLServerDatabaseMetaData#executeSPFkeys() only joining on the FK name and not the schema. Appending AND schema_id(t.FKTABLE_OWNER) = s.schema_id to the join seems to fix it.

Reproduction code

try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
    ResultSet rs = conn.getMetaData().getImportedKeys("TestDB", "s1", "FkTable1");

    System.out.println("Results:");
    // Prints out 2 rows with the same data when the table only has 1 FK
    while (rs.next())
        System.out.printf("%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\t%d\t%d\t%d\t%s\t%s\t%d\n",
                rs.getString("PKTABLE_CAT"), rs.getString("PKTABLE_SCHEM"), rs.getString("PKTABLE_NAME"), rs.getString("PKCOLUMN_NAME"),
                rs.getString("FKTABLE_CAT"), rs.getString("FKTABLE_SCHEM"), rs.getString("FKTABLE_NAME"), rs.getString("FKCOLUMN_NAME"),
                rs.getShort("KEY_SEQ"), rs.getShort("UPDATE_RULE"), rs.getShort("DELETE_RULE"),
                rs.getString("FK_NAME"), rs.getString("PK_NAME"), rs.getShort("DEFERRABILITY")
        );
}

[lilgreenbird]

hi @joel-ws,
Thanks for reporting the issue we'll take a look and get back to you.

[ulvii]

#1092 merged.