Bug in CTEs?
-
I am trying to write an inline splitter function based on Jeff Moden’s recent article,
http://www.sqlservercentral.com/articles/Tally+Table/72993/, but seem to be having problems with the CTEs. In the following test code options 1, 2, and 3 work but option 4 always fails with:
Msg 537, Level 16, State 2, Line 4
Invalid length parameter passed to the LEFT or SUBSTRING function.
The result of @@VERSION is:
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64) Feb 4 2011 11:27:06 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Is this a bug or am I just having a stupid day?
Any help would be appreciated.
DECLARE @pString varchar(8000) = 'report_name=Junk;identify=1'
,@pDelimiter char(1) = ';';
WITH E1(N)
AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)--10E+1 or 10 rows
,E2(N)
AS
(
SELECT 1 FROM E1 A, E1 B
) --10E+2 or 100 rows
,E4(N)
AS
(
SELECT 1 FROM E2 A, E2 B
) --10E+4 or 10,000 rows
,N(N)
AS
(
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
) -- zero to lengh of string
,ElementStart(N)
AS
(
SELECT N+1
FROM N
WHERE SUBSTRING(@pString,N,1) = @pDelimiter
OR N = 0
)
,Strings
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY N) AS ItemNumber
,SUBSTRING(@pString, N, COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, N), 0) - N, 305)) AS Item
FROM ElementStart
)
,AttribValues
AS
(
SELECT ItemNumber
,LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1), 50) AS Attribute
,SUBSTRING(Item, CHARINDEX('=', Item) + 1, 255) AS Value
FROM Strings
WHERE CHARINDEX('=', Item) > 0
)
-- Option1
--SELECT * FROM AttribValues
-- Option2
--SELECT * FROM AttribValues WHERE Attribute = 'identify'
-- Option3
-- SELECT Value FROM AttribValues
-- Option 4
SELECT Value FROM AttribValues WHERE Attribute = 'identify'
-
--Edit--
Misread post.
Very strange behaviour though.
--Edit 2--
If you change the AttribValues CTE to contain the clause, it works.
,AttribValuesAS
(
SELECT ItemNumber, Item
,LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1), 50) AS Attribute
,SUBSTRING(Item, CHARINDEX('=', Item) + 1, 255) AS Value
FROM Strings
WHERE CHARINDEX('=', Item) > 0 AND LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1), 50) = 'identify'
)
SELECT Value, Attribute FROM AttribValues --WHERE Attribute = 'identify'
Seems like a bug.
--Edit 3--
I look forward to someone else with more insight looking at this. . . because I'm confuzzled :hehe:
DECLARE @pString varchar(8000) = 'report_name=Junk;identify=1',@pDelimiter char(1) = ';';
WITH E1(N)
AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)--10E+1 or 10 rows
,E2(N)
AS
(
SELECT 1 FROM E1 A, E1 B
) --10E+2 or 100 rows
,E4(N)
AS
(
SELECT 1 FROM E2 A, E2 B
) --10E+4 or 10,000 rows
,N(N)
AS
(
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
) -- zero to lengh of string
,ElementStart(N)
AS
(
SELECT N+1
FROM N
WHERE SUBSTRING(@pString,N,1) = @pDelimiter
OR N = 0
)
,Strings
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY N) AS ItemNumber
,SUBSTRING(@pString, N, COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, N), 0) - N, 305)) AS Item
FROM ElementStart
)
,AttribValues
AS
(
SELECT ItemNumber, CHARINDEX('=', ISNULL(Item,' ')) -1 as Attribute, Item
--,LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1), 50) AS Attribute
,SUBSTRING(Item, CHARINDEX('=', Item) + 1, 255) AS Value
FROM Strings
WHERE CHARINDEX('=', Item) > 0
)
SELECT Value, Attribute,
CASE WHEN LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1),50) = 'identify'
THEN 1
ELSE 0 END
FROM AttribValues
That returns a 1 for one of the records. Which means that if you add a where clause to the query it should work (?)
DECLARE @pString varchar(8000) = 'report_name=Junk;identify=1',@pDelimiter char(1) = ';';
WITH E1(N)
AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)--10E+1 or 10 rows
,E2(N)
AS
(
SELECT 1 FROM E1 A, E1 B
) --10E+2 or 100 rows
,E4(N)
AS
(
SELECT 1 FROM E2 A, E2 B
) --10E+4 or 10,000 rows
,N(N)
AS
(
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
) -- zero to lengh of string
,ElementStart(N)
AS
(
SELECT N+1
FROM N
WHERE SUBSTRING(@pString,N,1) = @pDelimiter
OR N = 0
)
,Strings
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY N) AS ItemNumber
,SUBSTRING(@pString, N, COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pString, N), 0) - N, 305)) AS Item
FROM ElementStart
)
,AttribValues
AS
(
SELECT ItemNumber, CHARINDEX('=', ISNULL(Item,' ')) -1 as Attribute, Item
--,LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1), 50) AS Attribute
,SUBSTRING(Item, CHARINDEX('=', Item) + 1, 255) AS Value
FROM Strings
WHERE CHARINDEX('=', Item) > 0
)
SELECT Value, Attribute,
CASE WHEN LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1),50) = 'identify'
THEN 1
ELSE 0 END
FROM AttribValues
WHERE LEFT(SUBSTRING(Item, 1, CHARINDEX('=', Item) -1),50) = 'identify'
But no.
Msg 537, Level 16, State 2, Line 4Invalid length parameter passed to the LEFT or SUBSTRING function.
Good luck. 😉
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/ -
Hi Skcadavre,
Thanks for your efforts. I tried to do something quickly and thought I was losing the plot. (I can get it to work if I materialize the result but that defeats the point of an inline TVF.) Maybe the optimizer is trying to take a shortcut and getting confused.
I may look at it again but will probably end up by getting permision to use the CLR on this server.
Ken
-
This doesn't seem to have any affect on the sampel data you provided but the NULL check and the LENGH seem to be out of order:
,N(N)AS
(
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
) -- zero to lengh of string
I'd suggest a change like:
,N(N)AS
(
SELECT 0 UNION ALL
SELECT TOP (COALESCE(LEN(@pString),1)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
) -- zero to lengh of string
To the actual issue. I think your thoery is correct about the optimizer doing something that is causing an evalutation in a place you didn't expect. Even if you remove the CTE's and just use drived tables you get the same error. If I have a few spare cycles today I'll investigate a little further, assuming someone else doesn't figure it out first. 🙂
-
I found the issue. The filter for CHARINDEX('=', Item) > 0 was being evaluated after the SUBSTRING. I changed the AttribValues CTE to this and it works:
,AttribValuesAS
(
SELECT ItemNumber
--,LEFT(SUBSTRING(Strings.Item, 1, CHARINDEX('=', Strings.Item) -1), 50) AS Attribute
,CASE
WHEN CHARINDEX('=', Strings.Item) > 0
THEN LEFT(SUBSTRING(Strings.Item, 1, CHARINDEX('=', Strings.Item) -1), 50)
ELSE ''
END AS Attribute
--,SUBSTRING(Strings.Item, CHARINDEX('=', Strings.Item) + 1, 255) AS Value
,CASE
WHEN CHARINDEX('=', Strings.Item) > 0
THEN SUBSTRING(Strings.Item, CHARINDEX('=', Strings.Item) + 1, 255)
ELSE ''
END AS Value
FROM Strings
WHERE CHARINDEX('=', Item) > 0
)
-
Ken McKelvey (6/24/2011)
I am trying to write an inline splitter function based on Jeff Moden’s recent article,...It seems that things have gotten a bit complex on this problem and I might be able to help. Consider the following code snippet from the original post on this thread...
DECLARE @pString varchar(8000) = 'report_name=Junk;identify=1',@pDelimiter char(1) = ';';
We could make this quite simple but I need to know a little more about the problem, please.
1. Is the whole goal of this to simply to accept a parameter of unknown length and content with multiple "elements" where individual element data will always be in the form of "ElementName=ElementValue"? For example, there could easily be a 3 element string passed which looks like the following:
@pString = 'ElementName1=ElementValue1;ElementName2=ElementValue2;ElementName3=ElementValue3;'2. Considering the example string given in Item 1 above, is the goal to split that string into an NVP (Name/Value Pair) table that looks like the following?
ElementNumber ElementName ElementValue------------- ------------ -------------
1 ElementName1 ElementValue1
2 ElementName2 ElementValue2
3 ElementName3 ElementValue3
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok... assuming that everything I asked about in the post above is true, then the following code is a super simple way to solve the problem from the original post. As usual with my code, the details of how it works are commented in the code. In fact, there's more documentation in the code than there is code. 😉
-------------------------------------------------=====================================================================================================================
-- Simululate an input parameter.
-- The code below could easily be turned into a high performance iTVF (inline Table Valued Function)
-- to make consistent programming a whole lot easier.
--=====================================================================================================================
--===== Define the simulated input parameter
-- Note that we haven't identified any delimiter, yet.
DECLARE @pString VARCHAR(8000)
;
--===== Populate the simululated input parameter
SELECT @pString = 'ElementName1=ElementValue1;ElementName2=ElementValue2;ElementName3=ElementValue3'
;
--=====================================================================================================================
-- Change the delimiters in the input parameter string to make life real simple.
--=====================================================================================================================
--===== Since we know that each element and every element consists of 2 and only 2 parts,
-- we can replace the two different delimiters in the string with the a common delimiter.
-- This is where a "special" delimiter comes in. We use such a delimiter because the
-- chances of a user or application using the character as something meaningful is slim
-- to none and "Slim just left". ;-)
-- This method is explained at the following URL:
-- http://www.sqlservercentral.com/articles/T-SQL/63003/
-- To find out what CHAR(31) is, please see the following URL:
SELECT @pString = REPLACE(REPLACE(@pString,'=',CHAR(31)),';',CHAR(31))
;
--=====================================================================================================================
-- And, now, the problem becomes simple to solve and is nasty fast.
-- If you convert the code in this post to an iTVF, you can use the output directly in a FROM clause either
-- directly or to create a Temp Table to reference more than once.
--=====================================================================================================================
WITH
cteParseString AS
( --=== Split and unpivot the string marking both the Element "Number" and "Part"
-- Get the updated code for the DelimitedSplit8K function from the "Resources" link
-- at the bottom of the following article:
-- http://www.sqlservercentral.com/articles/Tally+Table/72993/
SELECT SplitItemNumber = split.ItemNumber,
ElementNumber = (split.ItemNumber-1)/2+1,
ElementPart = (split.ItemNumber-1)%2+1,
split.Item
FROM dbo.DelimitedSplit8K(@pString,@Delimiter) split
) --=== Now, reassemble the data into a "table result" using a high performance CROSS TAB.
SELECT ElementNumber,
ElementName = MAX(CASE WHEN ElementPart = 1 THEN Item ELSE '' END),
ElementValue = MAX(CASE WHEN ElementPart = 2 THEN Item ELSE '' END)
FROM cteParseString
GROUP BY ElementNumber
ORDER BY ElementNumber
;
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry... Almost forgot. The output from the above code looks like this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry for not getting back earlier but I was diverted.
Lamprey13 - Thanks for working out what was wrong.
Jeff - Thanks for your code which is exactly what I was looking for.
-
Ken McKelvey (7/2/2011)
Sorry for not getting back earlier but I was diverted.Lamprey13 - Thanks for working out what was wrong.
Jeff - Thanks for your code which is exactly what I was looking for.
You're welcome, Ken. Thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply