So I'm writing a large UDF in T-SQL to be used in reporting. The UDF contains quite a few common table expressions.

At one point I was adding another CTE:

 cteCmtCauses AS (
    SELECT ProductId = p.Id,
           Name = hz.Name,
           CMT = CONCAT(IIF(hz.Cmt_c= '1', 'C', ''), IIF(hz.hz.Cmt_m = '1', 'M', ''), IIF(hz.Cmt_t = '1', 'R', ''))
    FROM [redacted]

    UNION ALL

    SELECT ProductId = p.Id,
           Name = c.Name,
           --C = c.Cmr_HasCarcinogenicRisk,
           --M = c.Cmr_HasMutagenicRisk,
           --R = c.Cmr_HasToxicForReproductionRisk,
           CMT = CONCAT(IIF(hz.Cmt_c= '1', 'C', ''), IIF(hz.hz.Cmt_m = '1', 'M', ''), IIF(hz.Cmt_t = '1', 'R', ''))
    FROM [redacted]
 ),

 cteCmtCausesConcat AS (
    SELECT ProductId = p.Id,
           ComponentIds = (
                -- Here the issue happens
                SELECT CONCAT(cte.CMT, N'|', cte.Name, dbo.QueryConcatenationString())
                FROM cteCmtCauses cte 
                WHERE cte.ProductId = p.Id
                FOR XML PATH(N''), TYPE
           )
    FROM [redacted]
 ),

When trying to persist the UDF mutation, I got this error:

Msg 468, Level 16, State 9, Procedure QueryProduct, Line 93
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "널㾍.鉀杫.....祉߾.䊙꛸.鈀杫..." in the concat operation.

And in fact on every attempt the message changed a little bit:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "࿠䚋.剀焩.....祉߾.䊙꛸.刀焩..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "꿠䥆.뉀洶.....祉߾.䊙꛸.눀洶..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "꿠洦.퉀洷.....祉߾.䊙꛸.툀洷..." in the concat operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "焐柘.牀䯏.....祉߾.䊙꛸.爀䯏..." in the concat operation.

I was able to work around it by using:

SELECT CONCAT(cte.CMT, N'|', cte.Name COLLATE Latin1_General_CI_AS, dbo.QueryConcatenationString())

But the weird thing is, everything in the database and tempdb has the same collation: database collation, being Latin1_General_CI_AS (except from one of the tables used in the UNION ALL which is Latin1_General_CS_AS).

Gist of full reproducible sample, make sure that the database collation is Latin1_General_CI_AS.

How do I properly resolve this issue, is this a known bug, and do I need to worry about SQL server silently corrupting my data once I start using this UDF?

Using

Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64) 
    Sep 23 2016 15:49:43 
    Copyright (c) Microsoft Corporation
    Business Intelligence Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

The Collation problem (separate from the error message resulting from the Collation problem) is due to the CONCAT built-in function not honoring Collation Precedence, and hence requiring all input parameters to be of the same Collation. Clearly you have one input parameter that is not of the same Collation as the rest. That parameter is cte.Name, which you have currently fixed via the COLLATE keyword.

We can simulate this scenario as follows. You can run it from any database. The default Collation for the Database that I am executing the following code in is: SQL_Latin1_General_CP1_CI_AS.

CREATE TABLE #TT (Col1 NVARCHAR(50) COLLATE SQL_EBCDIC278_CP1_CS_AS);
INSERT INTO #TT values ('something');


SELECT CONCAT('now this is ', tmp.Col1)
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 17
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
*/

And the following two queries even show that the Collation is evaluated per each input parameter, with the first input parameter setting the Collation to be used:

SELECT CONCAT('now this is ', tmp.Col1, N' else' COLLATE Latin1_General_100_CI_AS)
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 23
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "Latin1_General_100_CI_AS" in the concat operation.
*/

SELECT CONCAT('now this is ' COLLATE Latin1_General_100_CI_AS, tmp.Col1, N' else')
FROM #TT tmp;
/*
Msg 468, Level 16, State 9, Line 30
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and
    "SQL_EBCDIC278_CP1_CS_AS" in the concat operation.
Msg 468, Level 16, State 9, Line 30
Cannot resolve the collation conflict between "Latin1_General_100_CI_AS" and
    "SQL_Latin1_General_CP1_CI_AS" in the concat operation.
*/

This conflict can be fixed in two ways:

  1. Don't use the CONCAT built-in function. The main benefit of CONCAT is that it doesn't require each parameter to be a string type; it will handle the conversion to string internally. This is convenient if you have several non-string items to concatenate. But if you only have strings, then it doesn't provide any benefit, and probably even hurts performance to pass all of that stuff into the function. AND, by not using CONTACT, Collation Precedence will take over, and in most cases resolve the conflict automatically.

    SELECT 'now this is ' + tmp.Col1
    FROM #TT tmp;
    -- now this is something
    

    In this case, Collation Precedence will determine that the Collation of the tmp.Col1 column overrides the Collation of the string literal (which uses the "current" database's default Collation).

  2. Use the COLLATE clause (as you are already doing). There is nothing wrong with this approach as this is one of the uses of the COLLATE keyword.

    -- Force the Collation of the column in the temp table to match the "current" database:
    SELECT CONCAT('now this is ', tmp.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS)
    FROM #TT tmp;
    -- now this is something
    
    
    -- Force the Collation of the string literal to match the column in the temp table:
    SELECT CONCAT('now this is ' COLLATE SQL_EBCDIC278_CP1_CS_AS, tmp.Col1)
    FROM #TT tmp;
    -- now this is something
    

    In these two cases, the Collation to use is determined by the first input parameter, and either that one needs to be explicitly set to the Collation of the second parameter (bottom example) which in this case is taken from the columns definition. Or, the second parameter needs to be explicitly set to match the Collation of the first parameter (top example) which in this case is taken from the databases's default as it is a string literal.

  • Thanks for your answer and pointers. Indeed I was aware of the collation issue, but rather worried about some weird bug manifesting in SQL Server. – Sebazzz Jan 14 '17 at 11:48
  • @Sebazzz I wouldn't worry about data corruption here. It "looks" like a bug with the error message itself coming from .NET. But I can't imagine there being any silent corruption. I will try your repro script in a little bit. What is the language of the OS set to? Also, have you tried this without the CONCAT function and instead just using + as I suggested? – Solomon Rutzky Jan 14 '17 at 13:00
  • 1
    I was worrying about data corruption because SQL obviously outputs random memory. Using concatenation with + causes no issues. Language of the OS is in both cases English, and tried with Windows Server 2008R2 and Windows 10. – Sebazzz Jan 14 '17 at 13:19
  • @Sebazzz Please run the following query, in the database that has the problem, and post the results: SELECT os_language_version, DATABASEPROPERTYEX(DB_NAME(), 'LCID') AS [DatabaseLCID], SERVERPROPERTY('LCID') AS [ServerLCID], SERVERPROPERTY('SqlCharSet') AS [SqlCharSet], SERVERPROPERTY('SqlCharSetName') AS [SqlCharSetName], SERVERPROPERTY('SqlSortOrder') AS [SqlSortOrder], SERVERPROPERTY('SqlSortOrderName') AS [SqlSortOrderName], SERVERPROPERTY('CollationID') AS [CollationID], SERVERPROPERTY('Collation') AS [Collation] FROM sys.dm_os_windows_info;. Thanks. – Solomon Rutzky Jan 14 '17 at 15:44

If you're looking for a quick workaround then COLLATE DATABASE_DEFAULT will help you get things moving again.

  • This is no different than what the O.P. is already doing. The only difference is that the O.P. specified the Database's default Collation. – Solomon Rutzky Jan 13 '17 at 15:53
  • 1
    @srutzky: The O.P. is specifying the collation and could be choosing the wrong one, database_default automatically figures out which collation is required. – pacreely Jan 13 '17 at 16:02
  • That is not entirely correct. The O.P. stated: "I was able to work around it by using...", hence the O.P. did choose the correct one. And DATABASE_DEFAULT will only be correct if that is the Collation that is needed. In this case that is the desired Collation, but that won't always be the case. And if there were parameters to the CONCAT function of other Collations, then the "preferred" one could be any of them. It just so happens that here, 3 of the 4 parameters are the database default simply due to being string literals and a UDF return value. But that is this particular case. – Solomon Rutzky Jan 13 '17 at 16:10
  • 1
    @srutzky: If the O.P. doesn't have the option of changing the collation of the offending table (properly fix) then database_default is a more generic solution that will deal with any other collation issues. I'm not providing a definitive answer, I'm just adding a contribution to the community. – pacreely Jan 13 '17 at 16:34
  • Tables do not have Collations, columns (and string expressions and variables) do. Either way, why would the O.P. change the collation of the column? There is nothing inherently wrong with the current collation. It is quite likely just fine for that column's typical usage. It just so happens that in this case it is in conflict, and that is fine as it happens often enough. But saying that DATABASE_DEFAULT will "will deal with any other collation issues" is just plain wrong. It could just as easily be that the opposite is needed, that all other parameters should use the columns collation. – Solomon Rutzky Jan 13 '17 at 16:44

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.