Driver version or jar name

6.3.6-preview

SQL Server version

SQL Server 2008, database schema in server server 2005 compatibility mode

Client operating system

Windows (but this should not matter)

Java/JVM version

1.8 (but this should not matter)

Table schema

any

Problem description

While using class SQLServerDatabaseMetaData the execution of the follwing query in Method getResultSetForForeignKeyInformation(...) leads an exception:

stmt.addBatch("insert into " + foreign_keys_combined_tableName
+ " select " + sys_foreign_keys + ".name, " + sys_foreign_keys + ".delete_referential_action_desc, " + sys_foreign_keys + ".update_referential_action_desc,"
+ fkeys_results_tableName + ".PKTABLE_QUALIFIER," + fkeys_results_tableName + ".PKTABLE_OWNER," + fkeys_results_tableName + ".PKTABLE_NAME," + fkeys_results_tableName + ".PKCOLUMN_NAME,"
+ fkeys_results_tableName + ".FKTABLE_QUALIFIER," + fkeys_results_tableName + ".FKTABLE_OWNER," + fkeys_results_tableName + ".FKTABLE_NAME," + fkeys_results_tableName + ".FKCOLUMN_NAME,"
+ fkeys_results_tableName + ".KEY_SEQ," + fkeys_results_tableName + ".UPDATE_RULE," + fkeys_results_tableName + ".DELETE_RULE," + fkeys_results_tableName + ".FK_NAME," + fkeys_results_tableName + ".PK_NAME,"
+ fkeys_results_tableName + ".DEFERRABILITY from " + sys_foreign_keys
+ " right join " + fkeys_results_tableName + " on " + sys_foreign_keys + ".name=" + fkeys_results_tableName + ".FK_NAME");

Expected behavior and actual behavior

Excected behaviour: query executes without exception and returns foreign key metadata.
Actual behavior:
SQLException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Repro code

I think the problem is that the database server collection is set to SQL_Latin1_General_CP1_CI_AS but the database collection is Latin1_General_CI_A. Since i think my jdbc connection inherits collation setting from database/schema instance. The table fkeys_results_tableName is created with collation Latin1_General_CI_AS but the system table sys.foreign_keys is created with collation SQL_Latin1_General_CP1_CI_AS. Therefore the equal operator in the expression ( sys_foreign_keys + ".name=" + fkeys_results_tableName + ".FK_NAME") can not be executed without additions.

Proposed fix: adding suffix " collate database_default" to sql query like this:
... sys_foreign_keys + " right join " + fkeys_results_tableName + " on " + sys_foreign_keys + ".name=" + fkeys_results_tableName + ".FK_NAME" + " collate database_default"

In my tests this resolved the issue.