次の方法で共有

Unicode 列をバイナリ照合順序に型キャストする SQL Server クエリが正しくない結果を返す

この記事は、Unicode 列に対して定義され、Unicode 列を別のバイナリ照合順序に型キャストするcollateを含むIN句またはOR句がステートメントに含まれている場合に発生する問題を解決するのに役立ちます。

元の製品バージョン: SQL Server
元の KB 番号: 3053639

現象

SQL Server データベースには、次の条件に該当するテーブルがあります。

  • テーブルには Unicode 列が含まれています。 たとえば、テーブルには nchar(5) 列があります。
  • Unicode 列の照合順序は Latin1_General_BIN。
  • 同じ Unicode 列がインデックスの一部です。 ただし、このテーブルに対して実行される T-SQL ステートメントは、正しくない結果を返す可能性があります。 この問題は、次の条件に該当する場合に発生します。
    • T-SQL ステートメントには、同じ Unicode 列に対して定義されている IN 句または OR 句が含まれています。
    • T-SQL ステートメントには、Unicode 列を別のバイナリ照合順序に型キャストする collate が含まれています。

サンプルクエリ:

CREATE TABLE [dbo].[Table_1]([Col1] [smallint] NOT NULL,
[Col2] [nchar](5),
[Col3] [nchar](5) COLLATE Latin1_General_BIN NOT NULL, -- Col3 , a Unicode Column with "Latin1_General_BIN" collation
CONSTRAINT [PK__Table_1] PRIMARY KEY CLUSTERED -- Primary Key on all the 3 columns
([Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SELECT * FROM Table_1 WHERE Col1 = 1 AND Col2 = '1' AND Col3 COLLATE Chinese_PRC_BIN IN (N'1' ,N'2')  -- This statement using "IN" and "collate" might give incorrect results.
GO

SELECT * FROM Table_1 WHERE Col1 = 1 AND Col2 = '1' AND (Col3 COLLATE Chinese_PRC_BIN = N'1' OR Col3 COLLATE Chinese_PRC_BIN = N'2') -- This statement using "OR" and "collate" might give incorrect results.
GO

回避策

この問題を回避するには、Unicode 列 ( Symptoms セクションのサンプル クエリの Col3) が次のいずれかの条件を満たしていることを確認します。

  • データ型の char(5) または nvarchar(5)です。
  • 照合順序が必要な Chinese_PROC_BIN の同じ照合順序を使用して定義されます ( Chinese_PROC_BIN は単なる例であることに注意してください。他のバイナリ照合順序も適用されます)。
  • Latin1_General_BIN以外の照合順序です。
  • CI 照合順序で照合されます。 (例: collate Chinese_PRC_90_CI_AI IN (N'1 ', N'2 '))。
  • 列の長さに一致する定数と比較されます。 たとえば、collate Chinese_PRC_BIN IN (N'1 ', N'2 ') のようにします。
  • インデックスの一部ではないか、 FORCESCAN テーブル ヒントを使用してテーブル スキャンが強制されます。
  • RTRIMやLTRIMなどの関数は、テーブルスキャンを強制するために使用されます。

詳細

この問題を再現するには、次のスクリプトを実行します。

CREATE DATABASE Test_DB
GO

USE Test_DB
GO

CREATE TABLE [dbo].[Table_1]([Col1] [smallint] NOT NULL,
[Col2] [nchar](5),
[Col3] [nchar](5) COLLATE Latin1_General_BIN NOT NULL, -- Col3 , a Unicode Column with "Latin1_General_BIN" collation

CONSTRAINT [PK__Table_1] PRIMARY KEY CLUSTERED -- Primary Key on all the 3 columns
([Col1] ASC,
[Col2] ASC,
[Col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Populate the table with a sample script as below

DECLARE @x AS INT
DECLARE @y AS INT

SET @x=1
SET @y=1

WHILE (@x<=2)
BEGIN
WHILE (@y<=1000)
BEGIN
INSERT INTO Table_1 values (@x,@y,@y)
SET @y=@y+1
END
SET @x=@x +1
END
GO

SELECT * FROM Table_1 WHERE Col1 = 1 AND Col2 = '1' AND Col3 COLLATE Chinese_PRC_BIN = N'1' -- Expected output of one row.
GO

SELECT * FROM Table_1 WHERE Col1 = 1 AND Col2 = '1' AND Col3 COLLATE Chinese_PRC_BIN IN (N'1' ,N'2') -- No rows returned when output for Col3= N'1' is expected.
GO

SELECT * FROM Table_1 WHERE Col1 = 1 AND Col2 = '1' AND (Col3 COLLATE Chinese_PRC_BIN = N'1' OR Col3 COLLATE Chinese_PRC_BIN = N'2') -- No rows returned when output for Col3= N'1' is expected.
GO

適用対象

  • SQL Server 2014 Business Intelligence
  • SQL Server 2014 Developer
  • SQL Server 2014 Enterprise
  • SQL Server 2014 Enterprise Core
  • SQL Server 2014 Express
  • SQL Server 2014 Standard
  • SQL Server 2014 Web
  • SQL Server 2012 Business Intelligence
  • SQL Server 2012 Developer
  • SQL Server 2012 Enterprise
  • SQL Server 2012 Express
  • SQL Server 2012 Standard
  • SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
  • SQL Server 2008 R2 Datacenter
  • SQL Server 2008 R2 Developer
  • SQL Server 2008 R2 Enterprise
  • SQL Server 2008 R2 Express
  • SQL Server 2008 R2 並列データ ウェアハウス
  • SQL Server 2008 R2 Standard
  • SQL Server 2008 R2 Web
  • SQL Server 2008 R2 Workgroup
  • SQL Server 2008 Developer
  • SQL Server 2008 Enterprise
  • SQL Server 2008 Express
  • SQL Server 2008 Standard
  • SQL Server 2008 Web
  • SQL Server 2008 Workgroup
  • SQL Server 2005 Developer Edition
  • SQL Server 2005 Enterprise Edition
  • SQL Server 2005 Express Edition
  • SQL Server 2005 Standard Edition
  • SQL Server 2005 Standard X64 Edition
  • SQL Server 2005 Workgroup Edition