I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005 bug when using LIKE searches

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.

USE tempdb GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Projects]( [id] [INT] IDENTITY(1,1) NOT NULL, [ProjectNumber] [VARCHAR](12) NULL, CONSTRAINT [PK_Project_Id] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO CREATE NONCLUSTERED INDEX [IX_Projects_ProjectNumber] ON [dbo].[Projects] ( [ProjectNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET NOCOUNT ON; SET XACT_ABORT ON; GO SET IDENTITY_INSERT [dbo].[Projects] ON; INSERT INTO [dbo].[Projects]([id], [ProjectNumber]) SELECT '6', 'P08.781' UNION ALL SELECT '5', 'P08.794' UNION ALL SELECT '4', 'P08.795' UNION ALL SELECT '2', 'P08.796' UNION ALL SELECT '3', 'P08.798' UNION ALL SELECT '1', 'P08.799' UNION ALL SELECT '7', 'P08.871' UNION ALL SELECT '8', 'P08.872' UNION ALL SELECT '9', 'P08.873' UNION ALL SELECT '10', 'P08.874' UNION ALL SELECT '11', 'P08.875' UNION ALL SELECT '12', 'P08.876' UNION ALL SELECT '13', 'P08.877' UNION ALL SELECT '14', 'P08.878' UNION ALL SELECT '15', 'P08.879' UNION ALL SELECT '16', 'P08.891' UNION ALL SELECT '17', 'P08.892' UNION ALL SELECT '18', 'P08.893' UNION ALL SELECT '19', 'P08.894' UNION ALL SELECT '20', 'P08.895' GO SET IDENTITY_INSERT [dbo].[Projects] OFF; -- NO ROWS should be 5 SELECT * FROM Projects WHERE ProjectNumber LIKE N'P08.79%' -- 5 ROWS SELECT * FROM Projects WHERE ProjectNumber LIKE N'P08.79%' COLLATE Latin1_General_CI_AI -- NO ROWS should be 5 SELECT * FROM Projects WHERE ProjectNumber LIKE N'P08.89%' -- 5 ROWS SELECT * FROM Projects WHERE ProjectNumber LIKE N'P08.89%' COLLATE Latin1_General_CI_AI -- 1 ROW SELECT * FROM Projects WHERE ProjectNumber LIKE N'P08.78%' -- 9 ROWS SELECT * FROM Projects WHERE ProjectNumber LIKE N'P08.87%' GO DROP TABLE [dbo].[Projects]

 

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
 

Print | posted on Monday, July 14, 2008 11:43 AM |

Feedback

Gravatar

# 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
7/14/2008 1:00 PM | Geyzerskiy Dmitriy
Gravatar

# 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.
7/14/2008 1:21 PM | Mladen
Gravatar

# 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?
7/16/2008 3:19 AM | Jason Adams
Gravatar

# 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?
7/16/2008 10:57 AM | Mladen
Gravatar

# 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?
7/16/2008 4:50 PM | Brad Ashforth
Gravatar

# 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.
7/16/2008 5:24 PM | Mladen
Gravatar

# 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.
7/28/2008 12:29 PM | Serge
Gravatar

# re: SQL Server 2005 bug when using LIKE searches

thanx Serge!
7/28/2008 12:35 PM | Mladen
Gravatar

# 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
9/2/2008 2:34 PM | HMich
Gravatar

# re: SQL Server 2005 bug when using LIKE searches

Thanks a million! We found the bug too!
4/1/2009 8:58 AM | Eric
Gravatar

# re: SQL Server 2005 bug when using LIKE searches

thik ba...............
8/17/2010 2:01 PM | Khan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET