May 20, 2010 at 6:33 am
Hi,
Following query has not any errors:
DECLARE @phrase VARCHAR(200);
SET @phrase = 'book,paper,computer';
SELECT word, word_order ,'%,' + word + ',%'
FROM (SELECT SUBSTRING(@phrase, n, CHARINDEX(',', @phrase + ',', n) -n) as word, n
FROM (SELECT CAST(D1.i AS VARCHAR(1)) + CAST(D2.j AS VARCHAR(1)) + CAST(D3.k AS VARCHAR(1)) + 0
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D1(i)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D2(j)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D3(k)
)D(n)
WHERE n <= LEN(@phrase)
) d(word, word_order)
Now, I add a WHERE clause to the outer query like this:
WHERE ',' + @phrase + ',' LIKE '%,' + word + ',%'
But an error appear:
Msg 537, Level 16, State 2, Line 4
Invalid length parameter passed to the LEFT or SUBSTRING function.
Why? how can I solve the problem?
May 20, 2010 at 7:06 am
I tried with 'Tally' table...
DECLARE @phrase VARCHAR(200);
SET @phrase = ',' + 'book,paper,computer' +',';
SELECT word, word_order ,'%,' + word + ',%'
FROM (SELECT SUBSTRING(@phrase, n, CHARINDEX(',', @phrase , n) -n) as word, n
FROM Tally
WHERE n <= LEN(@phrase)
) d(word, word_order)
WHERE @phrase LIKE '%,' + word + ',%'
The above code worked fine for me.
I got the below output.
book 2 %,book,%
paper 7 %,paper,%
computer 13 %,computer,%
is this the one you are expecting?
If you haven't created 'tally' table use master.dbo.spt_values table
DECLARE @phrase VARCHAR(200);
SET @phrase = ',' + 'book,paper,computer' +',';
SELECT word, word_order ,'%,' + word + ',%'
FROM (SELECT SUBSTRING(@phrase, number, CHARINDEX(',', @phrase , number) -number) as word, number
FROM master.dbo.spt_values
WHERE number <= LEN(@phrase) AND Type = 'P'
) d(word, word_order)
WHERE @phrase LIKE '%,' + word + ',%'
karthik
May 20, 2010 at 7:15 am
Have you aware had the division by zero error in the code like that:
SELECT A/B FROM TABLE1 WHERE B!=0
No? Welcome to Microsoft SQL Server version 2005 and higher query optimiser.
IT MAY HAPPEN! As optimiser may deside to evaluate devision result before applying WHERE filter (or JOIN) and return the error!
There are few ways to ensure that this code will not fail:
1. Filter out unwanted records as a first step (by deleting unwonted or inserting right ones into the temp table) before making calcuation (or data converion)
2. Ensutre that the expression can never cause the error, for example: A/NULLIF(B,0)
In your case you will need to do this:
DECLARE @phrase VARCHAR(200);
SET @phrase = 'book,paper,computer';
SELECT word, word_order ,'%,' + word + ',%'
FROM (SELECT CASE WHEN CHARINDEX(',', @phrase + ',', n) -n<0 THEN '' else
SUBSTRING(@phrase, n, CHARINDEX(',', @phrase + ',', n) -n) end as word, n
FROM (SELECT CAST(D1.i AS VARCHAR(1)) + CAST(D2.j AS VARCHAR(1)) + CAST(D3.k AS VARCHAR(1)) + 0
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D1(i)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D2(j)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D3(k)
)D(n)
WHERE n <= LEN(@phrase)
) d(word, word_order)
WHERE ',' + @phrase + ',' LIKE '%,' + word + ',%'
You will scream : But my CHARINDEX(',', @phrase + ',', n) -n is never less then 0.
But, optimiser thinks different. It can see that the n is a number from 0 to whatever, so if your @phrase is empty and n is > 0 then, potentialy, CHARINDEX(',', @phrase + ',', n) -n will be negative making the SUBSTRING to fail.
I personally "LOVE" it.
P.S Actually CASE WHEN statement may not always help. As in my example with devision by zero...
Cheers,
Me
May 20, 2010 at 7:21 am
Welcome to Microsoft SQL Server version 2005 and higher query optimiser.
IT MAY HAPPEN! As optimiser may deside to evaluate devision result before applying WHERE filter (or JOIN) and return the error!
Really! Do you have any proof for this? I just want to see that.
karthik
May 20, 2010 at 7:26 am
elutin (5/20/2010)
Have you aware had the division by zero error in the code like that:SELECT A/B FROM TABLE1 WHERE B!=0
No? Welcome to Microsoft SQL Server version 2005 and higher query optimiser.
IT MAY HAPPEN! As optimiser may deside to evaluate devision result before applying WHERE filter (or JOIN) and return the error!
There are few ways to ensure that this code will not fail:
1. Filter out unwanted records as a first step (by deleting unwonted or inserting right ones into the temp table) before making calcuation (or data converion)
2. Ensutre that the expression can never cause the error, for example: A/NULLIF(B,0)
In your case you will need to do this:
DECLARE @phrase VARCHAR(200);
SET @phrase = 'book,paper,computer';
SELECT word, word_order ,'%,' + word + ',%'
FROM (SELECT CASE WHEN CHARINDEX(',', @phrase + ',', n) -n<0 THEN '' else
SUBSTRING(@phrase, n, CHARINDEX(',', @phrase + ',', n) -n) end as word, n
FROM (SELECT CAST(D1.i AS VARCHAR(1)) + CAST(D2.j AS VARCHAR(1)) + CAST(D3.k AS VARCHAR(1)) + 0
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D1(i)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D2(j)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D3(k)
)D(n)
WHERE n <= LEN(@phrase) and n > 0
) d(word, word_order)
WHERE ',' + @phrase + ',' LIKE '%,' + word + ',%'
You will scream : But my CHARINDEX(',', @phrase + ',', n) -n is never less then 0.
But, optimiser thinks different. It can see that the n is a number from 0 to whatever, so if your @phrase is empty and n is > 0 then, potentialy, CHARINDEX(',', @phrase + ',', n) -n will be negative making the SUBSTRING to fail.
I personally "LOVE" it.
P.S Actually CASE WHEN statement may not always help. As in my example with devision by zero...
Cheers,
Me
I ran your code and got 4 rows ( 1 duplicate)
book 0 %,book,%
book 1 %,book,%
paper 6 %,paper,%
computer 12 %,computer,%
A small modification in the code.
DECLARE @phrase VARCHAR(200);
SET @phrase = 'book,paper,computer';
SELECT word, word_order ,'%,' + word + ',%'
FROM (SELECT CASE WHEN CHARINDEX(',', @phrase + ',', n) -n <0 THEN '' else
SUBSTRING(@phrase, n, CHARINDEX(',', @phrase + ',', n) -n) end as word, n
FROM (SELECT CAST(D1.i AS VARCHAR(1)) + CAST(D2.j AS VARCHAR(1)) + CAST(D3.k AS VARCHAR(1)) + 0
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D1(i)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D2(j)
CROSS JOIN
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) D3(k)
)D(n)
WHERE n > 0 and n <= LEN(@phrase)
) d(word, word_order)
WHERE ',' + @phrase + ',' LIKE '%,' + word + ',%'
karthik
May 20, 2010 at 7:30 am
Proof?
That will not be easy. The only thing I can tell that we have experienced such "nice" features of SQL while I was participating in one of the Microsoft case study SQL Server projects in UK. It was building largest datawarehouse for one of the UK leading fin. organisation.
It didn't happen all the time. It would only happen for very very large tables (even with simple joins) or in a complex queries based on many sub-queries (as in the discussed case).
Anyone from this project can confirm my words.
May 20, 2010 at 7:34 am
I wonder if Jeff Moden ever experienced this issiue and have better explanatino of why this happens.
Will hope that he will read this post and comment on it...
May 20, 2010 at 7:56 am
Thank you,
I use ABS() function for solving the problem.
And put here some solution for splitting string, see:
SET NOCOUNT ON
DECLARE @S VARCHAR(8)='kill,ill'
;WITH c AS
(SELECT 1 AS n
UNION ALL
SELECT n+1 FROM c WHERE n < 100)
--Incorrect Solution
SELECT i, 0 AS k
FROM (SELECT SUBSTRING(@s, n, ABS(CHARINDEX(',', @S + ',', n) - n)), n
FROM c
WHERE n <= LEN(@s)
) d(i,n)
WHERE ',' + @S + ',' LIKE '%,' + i + ',%'
UNION ALL
--Correct Solution
SELECT i, 2
FROM (SELECT SUBSTRING(@s, n, ABS(CHARINDEX(',', @S + ',', n) - n)), n
FROM c
WHERE n <= LEN(@s)
) d(i,n)
WHERE SUBSTRING(',' + @S, n, 1) = ','
UNION ALL
--Correct Solution
SELECT DISTINCT i, 1 AS k
FROM (SELECT SUBSTRING(@s, n, ABS(CHARINDEX(',', @S + ',', n) - n)), n
FROM c
WHERE n <= LEN(@s)
) d(i,n)
WHERE ',' + @S + ',' LIKE '%,' + i + ',%'
ORDER BY k, i
/*
i k
-------- -----------
ill 0
ill 0
kill 0
ill 1
kill 1
ill 2
kill 2
*/
May 21, 2010 at 7:54 am
karthikeyan-444867 (5/20/2010)
Welcome to Microsoft SQL Server version 2005 and higher query optimiser.
IT MAY HAPPEN! As optimiser may deside to evaluate devision result before applying WHERE filter (or JOIN) and return the error!
Really! Do you have any proof for this? I just want to see that.
It is fairly common. The optimizer is free to evaluate things in the order of its choice. Using a CASE Statement is one of the only ways to guarantee that things like this won't happen. As an amusing side note using IIf in SSRS does NOT guarantee this, much to my annoyance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply