SQLServerException: Cannot insert the value NULL into column 'name'" when calling DatabaseMetaData.getImportedKeys

[eirikbakke]

Driver version or jar name

6.4.0 (bisection reveals bug introduced in 6.3.3)

SQL Server version

Microsoft SQL Server 2016 (SP1-GDR) (KB4019089) - 13.0.4206.0 (X64)
Developer Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 15063: ) (Hypervisor)

Client operating system

Windows 10

Java/JVM version

1.8.0_112

Table schema

WideWorldImporters/Application/Cities in the WideWorldImporters example shipped with SQL Server.

Problem description

"SQLServerException: Cannot insert the value NULL into column 'name'" when calling DatabaseMetaData.getImportedKeys("WideWorldImporters", "Application", "Cities")

Expected behavior and actual behavior

The call to getImportedKeys should not throw an exception.

Repro code

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;

public class MSSQLJDBCBugExhibit {
  @Test
  public void testImportedKeysCannotInsertNullBug() throws SQLException {
    Connection conn =
        DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=tempdb", "duser", "dpass");
    DatabaseMetaData meta = conn.getMetaData();
    System.out.println(meta.getDriverVersion());
    try (ResultSet rs = meta.getImportedKeys("WideWorldImporters", "Application", "Cities")) {
    }
    /* Output is:

6.4.0.0
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'name', table 'tempdb.dbo.#foreign_keys_combined_resultsd402f1e3-3f8f-4af6-9a03-6d31bf5c2b04__________________________________________________000000001BC6'; column does not allow nulls. INSERT fails.
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetForForeignKeyInformation(SQLServerDatabaseMetaData.java:1003)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getImportedKeys(SQLServerDatabaseMetaData.java:875)
	at com.ultorg.evaluator.MSSQLJDBCBugExhibit.testImportedKeysCannotInsertNullBug(MSSQLJDBCBugExhibit.java:17)
    */
  }
}

[eirikbakke]

The problem appears to happen when getImportedKeys is called for a table with foreign keys in a database different from that specified in the original connection JDBC URL.

In the above case, in SQLServerDatabaseMetaData.getResultSetForForeignKeyInformation, sys.foreign_keys returns zero rows (because it only includes tables in the current database?) while the fkeys_results table has at least one row (one for each foreign key). The outer RIGHT JOIN between the two tables then causes nulls to appear in the name, delete_referential_action_desc, and update_referential_action_desc columns. When these null values are inserted into foreign_keys_combined_results, the error occurs.

[rene-ye]

Hi @eirikbakke, I was able to to reproduce the issue you described with the 6.4.0 driver, but it seems the changes addressing #665 does not have this issue. Do you mind testing with the new changes and see if it resolves the problems.
Jars for testing.zip

[eirikbakke]

Yes, I can confirm the JAR you sent works. Thanks for your attention!

[rene-ye]

#677 has now been merged. Closing the issue.