SQL Server Error – Invalid Length Parameter in LEFT or SUBSTRING Function

sql serversql-server-2008

I'm running into the "Invalid length parameter passed to the LEFT or SUBSTRING function" error, but it goes away and the query works when I include the column I'm passing into those functions, any clue?

Doesn't work:

SELECT 
  SQT.QUOTATIONID, 
  UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
  --, Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
    ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
    ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
    ON D.PRIMARYCONTACTEMAIL = Email.RECID

Invalid length parameter passed to the LEFT or SUBSTRING function

Works:

SELECT 
  SQT.QUOTATIONID, 
  UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager 
  , Email.LOCATOR
FROM SALESQUOTATIONTABLE AS SQT
INNER JOIN HCMWORKER AS H
    ON SQT.WORKERSALESRESPONSIBLE = H.RECID
INNER JOIN DIRPARTYTABLE AS D
    ON H.PERSON = D.RECID
INNER JOIN LOGISTICSELECTRONICADDRESS AS Email
    ON D.PRIMARYCONTACTEMAIL = Email.RECID

This is continuously repeatable, and the only change I made to the query was included the "Email.LOCATOR" column.
This query was working for years and just randomly stopped working today. I'm pretty certain it's a data issue, but am still perplexed why selecting the Email.LOCATOR column fixes the issue.

Best Answer

I think the issue is similar to this one: Strange behaviour in TSQL function (parameter with int variable or NULL behaves differently)?

Specifically what Aaron Bertrand mentions in his answer:

... because you can't always rely on SQL Server filtering rows before attempting calculations

What I think happens is that Email.Locator has some values that do not contain a @. When these values are processed, the CHARINDEX() is 0 and the LEFT() is called with parameter -1, so the error is thrown.

Bu why the error is thrown in one query and not the other? It's likely because the two queries are executed with different plans. The optimizer chooses a different plan (due to the extra column or due different statistics than last month or for whatever reason) and all the values of the column are read (and the calculations are done) before the joins to the other tables.

To avoid the issue, I suggest you use CASE, replacing 

LEFT(Email.Locator, CHARINDEX('@', Email.Locator) - 1)

with:

LEFT(Email.Locator, CASE WHEN CHARINDEX('@', Email.Locator) > 0 
                         THEN CHARINDEX('@', Email.Locator) - 1
                         ELSE 0
                    END)
Related Question