TSQL throw weird error when commenting a line of text

Question

Here is a strange situation. Following code works perfectly if I run it as is, but if I comment the first CHARINDEX line, it throws an error.

Msg 536, Level 16, State 4, Line 1 Invalid length parameter passed to the RIGHT function.

SELECT 
DT.DataID
,RIGHT(K.IniValue, LEN( K.IniValue ) - 15)
--error if you comment the next line
 ,CHARINDEX('}',SUBSTRING(K.IniValue,CHARINDEX('ExtShared',K.IniValue) + 12,LEN(K.IniValue))) AS LastPosition
  ,REPLACE( CONCAT( REPLACE( RIGHT(K.IniValue, LEN( K.IniValue ) - 15), '''}', '' ), SUBSTRING( P.providerData, CHARINDEX( '''providerInfo''=''', P.providerData ) + 16, CHARINDEX( ''',''storage', P.providerData ) - ( CHARINDEX( '''providerInfo''=''', P.providerData ) + 16 ))), '\\', '\' )
FROM llprod.DTreeCore AS DT
INNER JOIN llprod.DVersData AS DV ON DV.DocID = DT.DataID AND DT.VersionNum = DV.Version
INNER JOIN llprod.ProviderData AS P ON DV.ProviderId = P.providerID
INNER JOIN llprod.KIni AS K ON K.IniKeyword = P.providerType
--WHERE ( k.IniValue LIKE '%{%' and K.IniValue LIKE '%}%' AND k.IniValue LIKE '%ExtShared%')
WHERE DT.DataiD = 123456
OPTION (RECOMPILE) 

Answer 1

I forget what the term is for this or how to articulate it properly (my apologies) but I've experienced something similar before where the function you're applying in the SELECT list is being analyzed over your data before the WHERE clause actually filters down the data.

You likely have a value for the K.IniValue field in the llprod.KIni entity that has a length less than 15 characters, which would result in an invalid index (a negative number) for the RIGHT() function. When you comment out the first CHARINDEX() field, a different execution plan is being generated that results in the above scenario I just mentioned, and therefore the RIGHT() function is breaking (even though your WHERE clause may actually filter out all cases that should cause it to break, because the RIGHT() function is being analyzed over the data first).

To fix this, you can put a CASE statement checking the LENGTH() of K.IniValue before applying the RIGHT() function, so you can direct the execution plan appropriately by explicitly telling it how to handle cases when the K.InitValue has a LENGTH() less than 15 characters, in a non-breaking manner.