SQL Server 2008 R2 bug or what?

Question

I am observing a strange behaviour. The following code that is related to conversion of a substring of nvarchar to tinyint works fine:

DECLARE @s nvarchar(50) = N'Line 1'
DECLARE @n tinyint = SUBSTRING(@s, 6, 10)

SELECT
        N'Line 1' AS explicit_nvarchar,
        @s AS nvarchar_variable,
        SUBSTRING(@s, 6, 10) AS nvarchar_substring
        , @n AS tinyint_var_converted_earlier
        , CAST(SUBSTRING(@s, 6, 10) AS tinyint) AS cast_sub_var
        , CAST(SUBSTRING(N'Line 1', 6, 10) AS tinyint) AS cast_nvarchar_substr

I can observe the following output in the Microsoft SQL Server Management Studio:

enter image description here .

However, the code that tries to do with the SELECT from the tables fails if the commented line is uncommented:

Select 
    [order].[identifier] As [order_identifier],
    [plan_data].[starts_on] As [from],
    [plan_data].[ends_on] As [to],
    [workplace].[identifier] AS line_identifier,
    SUBSTRING([workplace].[identifier], 6, 10) AS line_no_str
    --, CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) AS line_no
From 
    ...

With commented line... enter image description here

but with uncommented line (both for conversion to tinyint or int)...

enter image description here

Update: Strange. I have added the last line to the WHERE to check whether all lines contain the 'Line...

Where
    [plan].[identifier] = @lPlanIdentifier And
    (
        [plan_data].[starts_on] Between @fromUTC And @toUTC Or
        [plan_data].[ends_on] Between @fromUTC And @toUTC)
    AND LEFT([workplace].[identifier], 4) = N'Line'

... and all of a sudden it works also with the uncommented line. How can I discover what causes the problem?

Update2: I should have followed the Hamlet Hakobyan's answer exactly as it was published. When looking inside the table, I can see:

enter image description here

Are there only 4 rows? Or are there any that have a line_identifier that won't parse? — DaveShaw, Jan 22 '13 at 21:47
Do you have a row in the table that has an `identifier` value of `undefined` by chance? Can you show the entire contents of the table you are querying? — mellamokb, Jan 22 '13 at 21:48
Updated. The SELECT part contains only the lines that I show. The result with the commented line returns 698 rows without any problem. The strings are all `Line 1`, `Line 2`, `Line 3`, or `Line 4` (at least I am not aware of any different value). After uncommenting, it fails immediately. — pepr, Jan 22 '13 at 21:56
Short answer: no, this is not a bug. Similar problem [link](http://rusanu.com/2011/08/10/t-sql-functions-do-no-imply-a-certain-order-of-execution/). — Bogdan Sahlean, Jan 22 '13 at 22:17

score 4 · Accepted Answer · answered Jan 22 '13 at 21:53

4

You have value in identifier column for which SUBSTRING([workplace].[identifier], 6, 10) returns ined.

Try this query:

SELECT * FROM [workplace]
WHERE SUBSTRING([identifier], 6, 10) = N'ined'

answered Jan 22 '13 at 21:53

Hamlet Hakobyan

32,965
6
52
68

Good idea. But then it returns no records with commented line and fails the same way with uncommented line. See the update part at the end of the question about another try -- all records contain `Line...`. – pepr Jan 22 '13 at 22:09
1

All records can contain `Line`. For instance `Line ined`. Are you tried run given query? – Hamlet Hakobyan Jan 22 '13 at 22:15
+2. You are right. It was `N'Undefined'` that would not be the part of the result, but at some stage, the substring was calculated. – pepr Jan 22 '13 at 22:21

score 1 · Answer 2 · answered Jan 22 '13 at 22:19

As indicated by the comments and other answer(s), you have a value in [workplace].[identifier] for one or more rows that fails to cast to an INT. When you added the following to the WHERE clause, you eliminated the row(s) that has this data before it was converted in the SELECT clause:

LEFT([workplace].[identifier], 4) = N'Line'

Run the following to get a distinct list of values in [workplace].[identifier]

SELECT DISTINCT [workplace].[identifier]
FROM [workplace]

score 1 · Answer 3 · edited May 23 '17 at 12:27

Whenever you have an error converting a varchar to a number the easiest way to find the data is using IsNumeric. However IsNumeric doesn't mean IsInteger so its worth removing scientific or decimal formats as described by G Mastros in this answer

SELECT
    [order].[identifier] As [order_identifier],
    [plan_data].[starts_on] As [from],
    [plan_data].[ends_on] As [to],
    [workplace].[identifier] AS line_identifier,
    SUBSTRING([workplace].[identifier], 6, 10) AS line_no_str
    --, CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) AS line_no
FROM
  ...

WHERE 

   IsNumeric(SUBSTRING([workplace].[identifier], 6, 10) + '.0e0') = 0

If you can't fix it but you can use a default value than you could use a case statement to do the conversion only when its legal to do so.

SELECT
    [order].[identifier] As [order_identifier],
    [plan_data].[starts_on] As [from],
    [plan_data].[ends_on] As [to],
    [workplace].[identifier] AS line_identifier,
    CASE WHEN IsNumeric(SUBSTRING([workplace].[identifier], 6, 10) + '.0e0') = 1
             THEN CAST(SUBSTRING([workplace].[identifier], 6, 10) AS int) 
             ELSE Null --Default to null in this example
    END as line_no

SQL Server 2008 R2 bug or what?

3 Answers3