Mladen Prajdić Blog

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

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.

<!–

Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/

–>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
 

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...............