Sql-Server

SQL Server：無法解決“Latin1_General_CI_AS”和“之間的排序規則衝突”Garbag和G一種rb一種G和garbage'

  • February 25, 2020

所以我在 T-SQL 中編寫了一個大型 UDF 用於報告。UDF 包含相當多的公用表表達式。

有一次我正在添加另一個 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]
),

嘗試保持 UDF 突變時，出現此錯誤：

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.

事實上，在每次嘗試中，消息都會發生一些變化：

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.

我能夠通過使用來解決它：

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

但奇怪的是，數據庫和 tempdb 中的所有內容都具有相同的排序規則：數據庫排序規則，存在Latin1_General_CI_AS（除了UNION ALLwhich is中使用的表之一Latin1_General_CS_AS）。

完整可重現樣本的要點，確保數據庫排序規則Latin1_General_CI_AS。

如何正確解決此問題，這是一個已知錯誤，我是否需要擔心 SQL Server 在我開始使用此 UDF 後會靜默損壞我的數據？

使用

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)

更新

我終於有時間對此進行測試並能夠重現該問題。雖然不尊重排序規則優先級（在下面我的原始答案中指出）是一個問題，但這不是這個問題（儘管兩者都可能是由相同的潛在錯誤引起的）。這是我現在可以確認的：

  1. OP 的測試腳本（通過 Gist）不會產生此問題，因為它缺少一個關鍵部分：強制排序和表中的NVARCHAR“名稱”列之一與另一個不同。Component``Statement

  2. 實際錯誤是UNION ALL.

  3. 中的排序規則不匹配UNION ALL 應該在查詢到達函式之前就結束了查詢CONCAT，如果兩列都是VARCHAR，但如果至少有一列是NVARCHAR，那麼CONCAT函式會阻止查詢正確終止。

  4. CONCAT當涉及到查詢時（至少在這種情況下） ，查詢可能會以兩種方式表現不正確UNION ALL：

  5. 如果來自子查詢的列（包含排序規則不UNION ALL匹配的UNION ALL 操作”（哦，我們不要忘記錯誤消息中的垃圾排序規則名稱！）CONCAT

  6. 如果子查詢中的列（包含UNION ALL排序規則不匹配的 ）是函式的第一個參數CONCAT，那麼它實際上會成功，對函式返回的值使用數據庫的預設排序規則CONCAT。（見下面的最終測試案例）

  7. CONCAT內置函式自 SQL Server 2014 起已修復，因為從該版本開始，這種行為和“原始答案”（見下文）中顯示的不正確行為都無法重現（我在 2014、2016、2017 和2019）。

-- DROP TABLE #Mix;
CREATE TABLE #Mix
(
 [VC1]  VARCHAR(50)  COLLATE SQL_Latin1_General_CP437_CS_AS,
 [VC2]  VARCHAR(50)  COLLATE Azeri_Cyrillic_100_CS_AS_WS,
 [NVC1] NVARCHAR(50) COLLATE Frisian_100_CS_AI_KS,
 [NVC2] NVARCHAR(50) COLLATE Sami_Sweden_Finland_100_CI_AI
);
INSERT INTO #Mix ([VC1], [VC2], [NVC1], [NVC2]) VALUES (0xB0, 0xDE, 0xDE, 0xDE);
SELECT * FROM #Mix;
/*
VC1    VC2    NVC1    NVC2

░      Ю      Þ       Þ
*/



SELECT CONCAT(N'Both VARCHAR', sub.[WhatEva])
FROM   (
   SELECT [VC1]
   FROM #Mix
   UNION ALL
   SELECT [VC2]
   FROM #Mix
) sub([WhatEva]);
/*
Msg 457, Level 16, State 1, Line XXXXX
Implicit conversion of varchar value to varchar cannot be performed because the
  collation of the value is unresolved due to a collation conflict between
  "Azeri_Cyrillic_100_CS_AS_WS" and "SQL_Latin1_General_CP437_CS_AS" in
  UNION ALL operator.
*/


SELECT CONCAT(N'At least one NVARCHAR', sub.[WhatEva])
FROM   (
   SELECT [VC1]
   FROM #Mix
   UNION ALL
   SELECT [NVC1]
   FROM #Mix
) sub([WhatEva]);
/*
-- 2012
Msg 468, Level 16, State 9, Line XXXXX
Cannot resolve the collation conflict between "{db_default_collation}" and
  "堓.ꚤ鍛翹.堓.툀堗.툀堗.쓀姧.꺱䱷..꺱䱷......꺱䱷..툘堗.帎鍲翹.堓..錿翹..."
  in the concat operation.

-- 2014, 2016, 2017, 2019
Msg 451, Level 16, State 1, Line XXXXX
Cannot resolve collation conflict between "Frisian_100_CS_AI_KS" and
  "SQL_Latin1_General_CP437_CS_AS" in UNION ALL operator occurring in SELECT
  statement column 1.
*/


-- SUCCESS!?!?!?! Should be an error!!!
SELECT CONCAT(sub.[WhatEva], N':At least one NVARCHAR') AS [ConcatSuccessWTF?],
      SQL_VARIANT_PROPERTY(CONCAT(sub.[WhatEva], N':At least one NVARCHAR'),
                           'collation') AS [ResultingCollation]
FROM   (
   SELECT [VC1]
   FROM #Mix
   UNION ALL
   SELECT [NVC1]
   FROM #Mix
) sub([WhatEva]);
/*
-- 2012
ConcatSuccessWTF?          ResultingCollation

░:At least one NVARCHAR    {db_default_collation}
Þ:At least one NVARCHAR    {db_default_collation}


-- 2014, 2016, 2017, 2019
Msg 456, Level 16, State 1, Line XXXXX
Implicit conversion of nvarchar value to sql_variant cannot be performed because the
  resulting collation is unresolved due to collation conflict between
  "Frisian_100_CS_AI_KS" and "SQL_Latin1_General_CP437_CS_AS" in UNION ALL operator.
*/

 

這是一個已知的錯誤

不確定它是否在任何公共論壇上被注意到，但它必須在內部（微軟）被注意到，因為它在下一個版本（即 SQL Server 2014）中被修復，儘管在我在 SQL Server 上測試的任何 Service Pack 中都沒有2012，SP4 GDR (11.0.7462.6)。

如何正確解決此問題

如果您或其他任何人仍在使用 SQL Server 2012 並遇到此問題，最好從源頭解決排序規則衝突，即在UNION ALL操作中。只需選擇具有您不想要的排序規則的列的表，並在COLLATE那裡應用子句，以便UNION ALL操作自行成功，即使沒有CONCAT使用任何函式。這比COLLATE在CONCAT函式中指定 更好，因為這是在 的事實之後UNION ALL，在這種情況下應該失敗，但由於 中的錯誤而允許成功CONCAT。

原始答案

Collat​​ion 問題（與 Collat​​ion 問題導致的錯誤消息不同）是由於CONCAT內置函式不遵守 Collat​​ion Precedence，因此要求所有輸入參數具有相同的 Collat​​ion。顯然，您有一個輸入參數與其他參數的排序規則不同。該參數是cte.Name，您目前已通過COLLATE關鍵字修復該參數。

我們可以模擬這個場景如下。您可以從任何數據庫執行它。我正在執行以下程式碼的數據庫的預設排序規則是：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.
*/

下面的兩個查詢甚至顯示了根據每個輸入參數評估排序規則，第一個輸入參數設置要使用的排序規則：

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.
*/

可以通過兩種方式解決此衝突：

  1. 不要使用CONCAT內置函式。的主要好處CONCAT是它不需要每個參數都是字元串類型；它將在內部處理轉換為字元串。如果您有多個非字元串項要連接，這很方便。但是，如果您只有字元串，那麼它不會提供任何好處，甚至可能會損害將所有這些東西傳遞給函式的性能。並且，如果不使用CONTACT，排序規則將接管，並且在大多數情況下會自動解決衝突。
SELECT 'now this is ' + tmp.Col1
FROM #TT tmp;
-- now this is something

在這種情況下，排序規則優先級將確定tmp.Col1列的排序規則覆蓋字元串文字的排序規則（它使用“目前”數據庫的預設排序規則）。 2. 使用該COLLATE子句（正如您已經在做的那樣）。這種方法沒有任何問題，因為這是COLLATE關鍵字的用途之一。

-- 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

在這兩種情況下，要使用的排序規則由第一個輸入參數決定，或者需要明確設置為第二個參數的排序規則（底部範例），在這種情況下，該參數取自列定義。或者，需要顯式設置第二個參數以匹配第一個參數（頂部範例）的排序規則，在這種情況下，它取自數據庫的預設值，因為它是字元串文字。

如果您正在尋找一種快速的解決方法，那麼COLLATE DATABASE_DEFAULT將幫助您重新開始工作。

引用自：https://dba.stackexchange.com/questions/160975

相關問答

Sql-Server

SQL Server Try_Cast 返回 Null 值

  • December 21, 2021
檢視問答
Sql-Server

尋求幫助以了解此 SQL 腳本中發生的情況，該腳本顯示有關 SQL Server 自動增長事件的關鍵資訊

  • August 25, 2021
檢視問答
Sql-Server

在沒有函式的情況下以相同的順序拆分兩個分隔的字元串

  • November 23, 2020
檢視問答
Sql-Server

使用 T-SQL 測試字元串是否為回文

  • May 8, 2020
檢視問答
Sql-Server

曾經很快的慢標量函式

  • August 29, 2019
檢視問答
Sql-Server

ORDER BY 和比較混合的字母和數字字元串

  • May 14, 2019
檢視問答