The disappearing act of the "Invalid length parameter passed to the LEFT or SUBSTRING function" error 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.
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) &gt; 0 
                         THEN CHARINDEX('@', Email.Locator) - 1
                         ELSE 0
                    END)