Different in behavior between 2005 and 2008R2 with RIGHT in the where clause

Question

Post reply

Different in behavior between 2005 and 2008R2 with RIGHT in the where clause

michael 77302

Valued Member

Points: 62
More actions
June 26, 2013 at 6:17 am

#274489

Just found an odd piece of behavioral difference between SQL 2005 and SQL 2008R2. Any help would be appreciated.
I have a tally table with numbers up to 8000. Then I have an Inline TVF to split strings.
Prerequisites:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
IF EXISTS
(SELECT1
FROM INFORMATION_SCHEMA.TABLES
WHERETABLE_NAME = 'NUMBERS'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
)
BEGIN
DROP TABLE dbo.NUMBERS
END
GO
CREATE TABLE dbo.NUMBERS
(
Number smallint IDENTITY(1, 1) PRIMARY KEY
)
GO
SET NOCOUNT ON
WHILE 1 = 1
BEGIN
INSERT INTO dbo.NUMBERS DEFAULT VALUES
IF @@IDENTITY = 8000
BEGIN
BREAK
END
END
SET NOCOUNT OFF
GO
IF OBJECT_ID('func_SPLIT_STRING') IS NOT NULL
BEGIN
DROP FUNCTION dbo.[func_SPLIT_STRING]
END
GO
CREATE FUNCTION [dbo].[func_SPLIT_STRING]
(@INPUT VARCHAR(MAX)
,@DELIMITER CHAR(1) = '|'
)
RETURNS TABLE AS
RETURN
( SELECT
ROW_NUMBER() OVER (ORDER BY Number ASC) [ID]
,SUBSTRING(@INPUT, Number, CHARINDEX(@DELIMITER, @INPUT + @DELIMITER, Number) - Number) [DATA]
FROM dbo.NUMBERS
WHERE Number <= CONVERT(INT, LEN(@INPUT))
ANDSUBSTRING(@DELIMITER + @INPUT, Number, 1) = @DELIMITER
)
GO
Then consider the following code:
DECLARE @VALUES VARCHAR(MAX)
SET @VALUES = 'E62|E48|E47'
SELECT
CONVERT(INT, RIGHT(DATA, LEN(DATA) - 1))
,ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1))
FROM dbo.func_SPLIT_STRING(@VALUES, '|')
WHERE ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1)) > 0
This code runs fine on SQL Server 2005, but raises the following error on SQL 2008R2 (SP1).
Msg 536, Level 16, State 4, Line 5
Invalid length parameter passed to the RIGHT function.
Please note that the database I run this on is running in 2005 compatibility level (level 90), but changing it to 100 doesn't have any effect.
This is a function that we commonly use to split input. I can rewrite it as follows to avoid the error, but would prefer to find a solution in the func_SPLIT_STRING function to avoid having to rewrite a lot of SQL code.
Workaround on the query side:
DECLARE @VALUES VARCHAR(MAX)
SET @VALUES = 'E62|E48|E47'
SELECT
CONVERT(INT, RIGHT(DATA, LEN(DATA) - 1))
,ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1))
FROM dbo.func_SPLIT_STRING(@VALUES, '|')
WHERE CASE WHEN LEN(DATA) > 0 THEN ISNUMERIC(RIGHT(DATA, LEN(DATA) - 1)) END > 0
By this logic, it appears that in spite of the actual output from the inline TVF, SQL comes across a value where the LEN(DATA) is 0, leading to -1.
Any help would be very much appreciated, or even an explanation as to why SQL 2008R2 misbehaves like this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Can't really explain why, but change the function to: -

CREATE FUNCTION [dbo].[func_SPLIT_STRING]

(@INPUT VARCHAR(8000)

,@DELIMITER CHAR(1) = '|'

)

And it'll work.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

michael 77302 Valued Member Points: 62 More actions · Answer 2

Thanks for the quick reply. I'm at home now so can't test it, but the plot thickened a bit.

I tried another splitting function; DelimitedSplit8K from Jeff Moden's excellent post here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

It exhibits identical behavior and might be easier to reproduce. My post here shows it for that function as well.

http://www.sqlservercentral.com/Forums/FindPost1467641.aspx

michael 77302 (6/26/2013)
Late entry to the discussion. I ran into an issue with a similar string splitter, and tried to break down my findings on this.
The following code causes an error in SQL Server 2008R2:
DECLARE @VALUES VARCHAR(8000)
SET @VALUES = 'E62|E48|E47'
SELECT
Item
FROM dbo.[DelimitedSplit8K](@VALUES, '|')
WHERE ISNUMERIC(RIGHT(Item, LEN(Item) - 1)) > 0
The following works
DECLARE @VALUES VARCHAR(MAX)
SET @VALUES = 'E62|E48|E47'
SELECT
Item, ItemNumber
FROM dbo.[DelimitedSplit8K](@VALUES, '|')
WHERE ISNUMERIC(RIGHT(Item, LEN(Item) - 1)) > 0
Select * also works, as does only ItemNumber.
The odd thing is that none of the possible result sets give an Item with a length of 0 (that would result in the error). I think something gets optimized so that an error is prematurely raised.
If I add one more CTE level to the end of the splitter function, specifying that ItemNumber > 0, this error does not occur.

It seems that the optimizer messes up in SQL2008 if you don't put the ItemNumber in the selection. I guess putting a CTE in the end of the function that enforces ItemNumber > 1 does work, so that'll have to be a workaround for now.