SQL Server nchar bug?

by Matthew Noonan 20. March 2006 06:05

Can anyone explain this? I Googled around but didn't find any significant answers. The following code can be run against the Northwind database in SQL Server, where the CustomerID column is defined as nchar(5).

Apparently there is a bug when performing parameterized queries on columns using the LIKE clause, as in this example:

-- Returns no rows
SELECT * FROM [dbo].[Customers] WHERE [CustomerID] LIKE @CustomerID

However, this format works correctly:

-- Returns 39 rows
SELECT * FROM [dbo].[Customers] WHERE [CustomerID] LIKE '%A%'


As further proof of the bug, the NOT LIKE clause acts badly as well:

-- Returns all 92 rows
SELECT * FROM [dbo].[Customers] WHERE [CustomerID] NOT LIKE @CustomerID
-- Returns 53 rows
SELECT * FROM [dbo].[Customers] WHERE [CustomerID] NOT LIKE '%A%'

This was originally reported to me by Yu (Bruce) Houfang as an EasyObjects bug, but in reality it seems to be SQL Server's problem. However, I have checked in a work-around to the SqlDynamicQuery class so that if a nchar column is used in a LIKE clause, the direct string value is passed instead of the parameter.

Can anybody explain this behavior?

Tags:

General

Submit to DotNetKicks...
Permalink | Comments (10)

Comments

3/20/2006 2:00:00 AM #

jemis, thanks for the tip, I will try that out tonight. I really hate passing strings instead of parameters.

newtoncd, that's certainly possible, it's definitely somehow related to Unicode and the collation could affect that.

Thanks guys!

mgnoonan United States

3/20/2006 2:00:00 AM #

I tried all of the querys and all works for me without any problem.

Maybe, this problem is related with the Sort Order in default SQL installation. My SQL installation uses a different collation configuration (Sort Order 44) other then default.

newtoncd United States

3/20/2006 2:00:00 AM #


-- Returns no rows
SELECT * FROM [dbo].[Customers] WHERE [CustomerID] LIKE @CustomerID

-- This work
SELECT * FROM [dbo].[Customers] WHERE [CustomerID] LIKE RTRIM(@CustomerID)

jemis United States

3/21/2006 2:00:00 AM #

Thanks for that detailed research, trbcc!

I guess I overlooked an obvious resource (the documentation, go figure Smile when I came across the problem. I should have the source code on Tigris updated with the fix before too long.

mgnoonan United States

3/21/2006 2:00:00 AM #

I am wondering if  '%A%' is treated as '%A%  ' (i.e. two trailing spaces since it is of type nchar(5)) for @CustomerID

trbcc United States

3/21/2006 2:00:00 AM #

it also works if @CustomerID is of type varchar(5)

trbcc United States

3/21/2006 2:00:00 AM #

Please check the Remarks for the LIKE operator on T-SQL Reference (SQL Server Books Online):
A string comparison using a pattern containing char and varchar data may not pass a LIKE comparison because of how the data is stored. It is important to understand the storage for each data type and where a LIKE comparison may fail. The following example passes a local char variable to a stored procedure and then uses pattern matching to find all of the books by a certain author. In this procedure, the author's last name is passed as a variable.

CREATE PROCEDURE find_books @AU_LNAME char(20)
AS
SELECT @AU_LNAME = RTRIM(@AU_LNAME) + '%'
SELECT t.title_id, t.title
FROM authors a, titleauthor ta, titles t
WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id
   AND a.au_lname LIKE @AU_LNAME

In the find_books procedure, no rows are returned because the char variable (@AU_LNAME) contains trailing blanks whenever the name contains fewer than 20 characters. Because the au_lname column is varchar, there are no trailing blanks. This procedure fails because the trailing blanks are significant.

However, this example succeeds because trailing blanks are not added to a varchar variable

trbcc United States

3/21/2006 2:00:00 AM #

Sorry, typo: nvarchar(5)
i.e.
it also works if @CustomerID is of type nvarchar(5)

Anyway, in the case of the Northwind database, varchar(5) also works.

trbcc United States

3/29/2006 2:00:00 AM #

Yes, I believe it is. Sorry for not announcing it.

mgnoonan United States

3/29/2006 2:00:00 AM #

Matt: is this fixed on Tigris yet?

trbcc United States

Comments are closed