SQLException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

[thfranken]

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.

[cheenamalhotra]

Hi @thfranken

Good to know you found fix for collation conflict issue.

Regarding proposal on adding suffix " collate database_default":
This is something that drivers never perform and client applications must handle in their queries. Driver applications do not add prefix/suffix/modification of any type to SQL Queries passed to database for any reason. I hope I clarified your question.

Closing this issue here. Let us know if you have any more concerns.

[nvdweem]

Hi @cheenamalhotra,
I'm running into the same issue but I don't fully understand the response to the ticket.
I do understand that a client is responsible for handling collation stuff, but that's quite hard to do when the driver constructs and executes the query.

The fix that @thfranken proposes is a fix in how the query is constructed in the driver (com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData).

[cheenamalhotra]

Hi @nvdweem

I rechecked SQLServerDatabaseMetaData and yes the fix is for the temporary tables created by driver so needs to be fixed in the driver. I think I mixed it with external queries. Re-opening the issue, we'll work on fix for all related queries in the same class that are possibly likely to cause this exception.

Thanks for correction.

[tjlee]

+1

[rene-ye]

Hi @thfranken @nvdweem @tjlee, we recently had to change that part of the driver and thought now would be a good time to address this issue. I'm adding the solution to this issue along with the PR addressing #665. You can test the changes by cloning the branch from PR #677 or downloading the jars below.
Jars for testing.zip

[tjlee]

@rene-ye I can't reproduce the issue anymore with the latest provided jars.

[eirikbakke]

I see that you're working on the getResultSetForForeignKeyInformation code--I just reported another related bug at #681 .

[rene-ye]

#677 has now been merged. Closing the issue.