Saturday, January 23, 2010

Sql Server bug when using NULL with LEFT and CHARINDEX functions?

I have the following query:

select LEFT(null,CHARINDEX('.',NULL))

My server:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)

The query should return NULL, in my opinion. But it constantly throws an error:
Msg 536, Level 16, State 1, Line 1
Invalid length parameter passed to the left function.

It's not working on SQL Server 2008 also:
Microsoft SQL Server 2008 R2 (CTP) - 10.50.1092.20 (X64)

This message occurs if you try to pass a negative value to the LEFT function in sql server: SELECT LEFT('abc',-1).
But this is not the case here since CHARINDEX should return NULL: if either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or higher.

When I am breaking the query everything works as expected:
select CHARINDEX('.',NULL)
select LEFT(null,null)
both return NULL.

Initially I thought that it was a 64 bit issue, but I managed to reproduce it (harder, I admit) on 32 bit also.
You might even encounter the situation where it will work for an indefinte runs and all of a sudden crash. I have posted it on some sql server forums but I couldn't find a reasonable explanation. And going to Microsoft with it is not always easy. :)

1 comment:

  1. Your blog is much effective and thanks for sharing information.. Here we are providing training & materilas and dumps for those who are preparing for cca 175 spark and hadoop developer certification exam and in the related fields of cloudera hadoop developer certification exam.
    cca 175 certification
    cloudera hadoop developer certification
    cca 175 spark and hadoop developer certification
    hadoop developer certification