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. :)
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. :)
_1270.jpg)
No comments:
Post a Comment