The disappearing act of the "Invalid length parameter passed to the LEFT or SUBSTRING function" error

Question

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.

Answer 1

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)

Answer 2

In all likelihood, the issue is indeed a data issue. A new row has been added to the LOGISTICSELECTRONICADDRESS table, with a LOCATOR that has no "@" in it.

Changing the query means that the full value of LOCATOR has to be carried through to the final results. In that case, there's no particular advantage to performing the calculation until the final result set has been determined, so SQL Server is waiting until the end of the selection process to calculate the value of Manager.

Based on the results you're getting, without LOCATOR in the SELECT list, SQL Server is choosing to compute the value of Manager before deciding what the final result rows are. It's possible that value of Manager will be much smaller than the full value of LOCATOR, so calculating instead of carrying the full LOCATOR value forward would save memory. If the data from LOGISTICSELECTRONICADDRESS is joined into the result set data before some of the other tables are joined in, then the calculation could be performed on rows that won't be returned in the final result set.

---

You didn't ask how to fix this, but (for the sake of completeness), you should check the value returned by CHARINDEX. If you want rows where LOCATOR has no "@", you can use a CASE statement:

SELECT SQT.QUOTATIONID,
       CASE WHEN CHARINDEX('@', Email.Locator) > 0
         THEN UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1))
         ELSE ''
       END AS Manager
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

(Of course, you could return the full LOCATOR value instead of an empty string, that's your call)

If you don't want to see rows where the "@" is missing, you can just check in the WHERE clause:

SELECT SQT.QUOTATIONID,
       UPPER(LEFT(Email.LOCATOR, CHARINDEX('@', Email.Locator) - 1)) AS Manager
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
WHERE CHARINDEX('@', Email.Locator) > 1

Tested on one of my own tables; it worked fine there.