Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

SQL Server 2005 bug when using LIKE searches

by Mladen Prajdić

Here's a demo of a nice little bug in SQL Server 2005 when using LIKE comparisons for searching.

What is so "nice" about it is that it only manifests itself if your search ends in number 9 followed by a wildcard when

selecting only columns covered by a nonclustered index.

I've been able to reproduce the error on different collations and servers. They were all SQL Server 2005 SP2+. SQL Server 2000 isn't affected by this.

You can find the connect issue posted by my coworker here.

Craig Freedman pointed me to this connect issue that is similar and probably originates from the same bug.









 


Execution plan for the LIKE 'P08.89%' query:


LikeBugPlan 


And the same execution plan in text:
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
     |--Merge Interval
     |    |--Concatenation
     |         |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert(N'P08.89',NULL,(22))))
     |         |    |--Constant Scan
     |         |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeThroughConvert(NULL,N'P08.8?',(10))))
     |              |--Constant Scan
     |--Index Seek(OBJECT:([AdventureWorks].[dbo].[Projects].[IX_Projects_ProjectNumber]), 
     	  SEEK:([AdventureWorks].[dbo].[Projects].[ProjectNumber] > [Expr1010] AND 
     	        [AdventureWorks].[dbo].[Projects].[ProjectNumber] < [Expr1011]),  
          WHERE:(CONVERT_IMPLICIT(nvarchar(12),[AdventureWorks].[dbo].[Projects].[ProjectNumber],0) like N'P08.89%') 
          ORDERED FORWARD)


 


By looking at the query plan we can see that the internal GetRangeThroughConvert method is used to get the search range in the new range seek optimizations in SQL Server 2005. Of course I have no idea what this method does internally but my guess would be that it uses column statistics to plot the correct search range. You can read more about that in this this post by Craig Freedman who was nice enough to help me confirm that this issue is actually a bug.


This bug is apparently fixed in SQL Server 2008, but I haven't tested this. 


 
kick it on DotNetKicks.com 





  





 




Legacy Comments












Geyzerskiy Dmitriy

 
2008-07-14		
re: SQL Server 2005 bug when using LIKE searches 
 Hi,
ProjectNumber is defined as VARCHAR(12) but you refer to it by using unicode notation prefixing the search argument with N literal. If you either omit the 'N' or define ProjectNumber collumn as NVARCHAR(12) you will get the expected behavior.

SET ANSI_PADDING OFF prior to CREATE INDEX statement should be changed to SET ANSI_PADDING ON as well, otherwise SQL Server throws an error on index creation.

I'm using SQL Server 2005 SP2 with Cumulative Update Fix 7 (9.00.3239).
I tend to think that it is rather unpredictable behavior than bug.

Regards,
Dmitry		











Mladen

 
2008-07-14		
re: SQL Server 2005 bug when using LIKE searches 
 i'm aware of the varchar to nvarchar conversion and that shouldn't be an issue.
so no, this is a confirmed bug and not unexpected behavior.












Jason Adams

 
2008-07-16		
re: SQL Server 2005 bug when using LIKE searches 
 I run your code as is, and all results come back correctly:

id          ProjectNumber
----------- -------------
5           P08.794
4           P08.795
2           P08.796
3           P08.798
1           P08.799

id          ProjectNumber
----------- -------------
5           P08.794
4           P08.795
2           P08.796
3           P08.798
1           P08.799

id          ProjectNumber
----------- -------------
16          P08.891
17          P08.892
18          P08.893
19          P08.894
20          P08.895

id          ProjectNumber
----------- -------------
16          P08.891
17          P08.892
18          P08.893
19          P08.894
20          P08.895

id          ProjectNumber
----------- -------------
6           P08.781

id          ProjectNumber
----------- -------------
7           P08.871
8           P08.872
9           P08.873
10          P08.874
11          P08.875
12          P08.876
13          P08.877
14          P08.878
15          P08.879

Is there another unknown variable that we might not be aware of?		











Mladen

 
2008-07-16		
re: SQL Server 2005 bug when using LIKE searches 
 hi,
i've been able to reproduce this on every SQL Server 2005 with sp2 that we have here.
what is your server version, os language and default server collation?












Brad Ashforth

 
2008-07-16		
re: SQL Server 2005 bug when using LIKE searches 
 Does this occur only in Tempdb? I just tried it on one of our dev servers (std database, not TempDB) and this bug does not seem to occur?		











Mladen

 
2008-07-16		
re: SQL Server 2005 bug when using LIKE searches 
 no it doesn't occur only on tempdb.
our servers and all databases had either Latin1_General_CI_AS or Slovenian_CI_AI as default collations and it happened on all of them.












Serge

 
2008-07-28		
re: SQL Server 2005 bug when using LIKE searches 
 Thank you for the post.
I've reproduced the bug using Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)
I've also tried to run your script under SQL Server 2008 RC0: all queries return correct results.		











Mladen

 
2008-07-28		
re: SQL Server 2005 bug when using LIKE searches 
 thanx Serge!		











HMich

 
2008-09-02		
re: SQL Server 2005 bug when using LIKE searches 
 Hello,
I cannot reproduce the bug. All selects return the right number of rows 
This are the server details:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64)   Mar 23 2007 18:41:50   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) 
Server Collation: SQL_Latin1_General_CP1_CI_AS		











Eric

 
2009-04-01		
re: SQL Server 2005 bug when using LIKE searches 
 Thanks a million! We found the bug too!		











Khan

 
2010-08-17		
re: SQL Server 2005 bug when using LIKE searches 
 thik ba...............