June 26, 2013 at 6:17 am
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.
June 26, 2013 at 8:23 am
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.
June 26, 2013 at 9:14 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply