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.

  • 2
    Some of the values in that column do not have a @. The charindex is calculated as 0 and the LEFT() gets -1 as parameter. – ypercubeᵀᴹ Sep 19 at 20:21
  • Check your Messages tab on the "working" query. You're probably getting the same error. You just happen to be seeing results before you hit an error (as @ypercubeᵀᴹ) alluded to. When you add the extra column to the SELECT list, SQL Server retrieves the results in a different order, so you get some results to return first before hitting a failure. – mathewb Sep 19 at 20:29
  • No error in the messages tab. Messages tab says the following: (1750 rows affected) (1 row affected) Note, I verified that 1,750 results is the correct record count too. So I'm definitely getting every record back that I should be. – J.D. Sep 19 at 20:56
  • 3
    Problem with similar cause (I think): dba.stackexchange.com/questions/108131/… Specifically what Aaron mentions in his answer: "... because you can't always rely on SQL Server filtering rows before attempting calculations." – ypercubeᵀᴹ Sep 19 at 21:28
  • 2
    And it may make sense to use a constraint to prevent junk, non-email data from entering the column in the first place... or use a computed column... or store the parts of the email address separately... – Aaron Bertrand Sep 19 at 23:07
up vote 6 down vote accepted

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)
  • 2
    Probably a lookup is required when the other column is included in the output, or there is a better covering index for that case... – Aaron Bertrand Sep 19 at 23:00
  • Thanks for the explanation, this makes more sense. And I'm assuming something caused the query plan to be updated which is why this was working before today and then "randomly' broke. Also, thanks for the link to Aaron Bertrand's related answer. The last question I have is why don't order of clause operations apply here? I had a better source but couldn't find it, but essentially isn't this the order that clauses are processed? - quora.com/What-is-the-execution-order-of-SQL-queries And if so, why wouldn't the ON clause in the joins get hit first and filter out the problematic rows? – J.D. Sep 20 at 3:25
  • 2
    Yes, the logical order of execution is FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY. But that's only the logical order of execution. Optimizers are free to choose different paths though as long as the result is the same as if the logical order was followed. If all values had a @, then there wouldn't be a problem. Some people see this behaviour as a bug and I think there was a Connect item about it. – ypercubeᵀᴹ Sep 20 at 5:27
  • I have to say, the more I learn about the optimizer, the more I realize it's a mysterious creature. Thanks again for the info! – J.D. Sep 20 at 11:41

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.