SQL Server query that typecasts a Unicode column to a binary collation returns incorrect results

Applies to: SQL Server 2014 Business IntelligenceSQL Server 2014 Business IntelligenceSQL Server 2014 Developer

Symptoms


You have a table in a SQL Server database in which the following conditions are true:
  • The table contains a Unicode column. For example, the table has an nchar(5) column.
  • Collation of the Unicode column is “Latin1_General_BIN.”
  • The same Unicode column is part of an index.
However, T-SQL statements that are run against this table may return incorrect results. This problem occurs when the following conditions are true:
  • The T-SQL statement contains an “IN” or “Or” clause that's defined for the same Unicode column.
  • The T-SQL statement contains “collate” to typecast the Unicode column to another binary collation.

Sample query:

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

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. We are currently working on a fix for this problem. This article will be updated when additional information becomes available.

Workaround


To work around this problem, make sure that the Unicode column (Col3 in the sample query in the "Symptoms" section) meets one of the following conditions:
  • Is of the datatype char(5) or nvarchar(5).
  • Is defined by using the same collation of “Chinese_PROC_BIN” for which collate is desired (be aware that “Chinese_PROC_BIN” is just an example; other binary collations also apply).
  • Is of a collation other than "Latin1_General_BIN."
  • Is collated on CI collation. For example: collate Chinese_PRC_90_CI_AI IN (N'1 ', N'2 ').
  • Is compared with a constant that matches the column length. For example, collate Chinese_PRC_BIN IN (N'1 ', N'2 ').
  • Is not part of the Index, or a table scan is forced by using the FORCESCAN table hint. 
  • Functions such as RTRIM and LTRIM are used to force a table scan.

More Information


To reproduce this issue, run the following script:
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