sql server bug, configuration issue, different NULL handling 32-bit vs. 64-bit?

• Monica Nicoleta Pantaru

  SSC Journeyman

  Points: 90

  More actions

  April 10, 2009 at 4:29 am

  #136563

  Hi all!

  I have this query:

  SELECT LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS INT)-1))

  -- or it's simpler version

  SELECT LEFT(NULL,CHARINDEX('.',NULL)-1)

  On 32-bit machines it works fine (tested on 3 servers, with SQL 2005 SP2 CU#6, SP2 CU#8, SP3 on them).

  On 64-bit (3 machines, SP2 CU#6, CU#8, and SP3) it's failing (but not consistently) with the next error message:

  "Invalid length parameter passed to the left function"

  I am not trying to find a solution for it. I am trying to understand the root cause of the issue. Is it a sql

  server bug? A configuration issue? Anything else that I might be missing?

  Please note that these queries work:

  select LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS BIGINT)-1))

  select LEFT(NULL,ABS(CAST(ISNULL(CHARINDEX('.',NULL),NULL) AS INT)-1))

  Any input will be greatly appreciated.

  Thanks,

  Monica

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 10, 2009 at 7:43 am

  #975747

  Are you sure the issue is because it is 64bit? Are you doing some sort of check to make sure there is a '.' in the field?

  I assume the query is something like this:

  SELECT LEFT(MyField,ABS(CAST(CHARINDEX('.', MyField) AS INT)-1))

  If MyField does not contain a '.' the CHARINDEX('.', MyField) AS INT)-1) will return -1 which will result in the error you are seeing.

  Hope that helps.

  Sean

  I have this query:

  SELECT LEFT(NULL,ABS(CAST(CHARINDEX('.',NULL) AS INT)-1))

  -- or it's simpler version

  SELECT LEFT(NULL,CHARINDEX('.',NULL)-1)

  "Invalid length parameter passed to the left function"

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/