-1

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)
Michael Green
  • 24,839
  • 13
  • 51
  • 96
Nish
  • 1
  • 1
  • We cannot reproduce the error without the DDL for the tables. Please, add them so that we can better help you. – Ronaldo Feb 18 '21 at 01:12

1 Answers1

0

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.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • I think i found a value which doesn't contain any of the expressions mentioned on the CHARINDEX() statement. But those records wont be returned for those particular DataID. The part I dont understand is Why it is throwing an error when I comment the CHARINDEX line. Somehow SQL server is considering the value of that expression when it is not commented. – Nish Feb 18 '21 at 02:21
  • 1
    The issue isn't particularly related to your CHARINDEX() function rather commenting and uncommenting it just coincendentally results in a different execution plan. The issue is your data in the K.InitValue has a value for some row where the length is less than 15 characters. I bet if you ran a SELECT * FROM llprod.KIni WHERE LEN(InitValue) < 15 it'll return rows. If you add a CASE statement to your RIGHT() function call as my answer mentions, then your issue will go away. Again, it doesn't matter your predicates filter out those records from llprod.KIni that would break it... – J.D. Feb 18 '21 at 02:51
  • ...the RIGHT() function is being analyzed against the data before it's being filtered in the other execution plan. – J.D. Feb 18 '21 at 02:52
  • @Nish I just realized I forgot to tag you. Please see my previous comments. – J.D. Feb 18 '21 at 03:30
  • 1
    @JD, Thanks, you are right RIGHT() function is validated first. I compared the estimated execution plan. Above query does a TableScan -->Sort>Compute Scalar operation on KIni table. While with that column commented out, it performs TableScan ->ComputeScalar ->Sort . I guess the ComputeScalar operation is throwing the error. Its so weird seeing RIGHT() operation performing before WHERE clause – Nish Feb 18 '21 at 03:46
  • @Nish Indeed, it's unintuitive, and kind of goes against the order of events but there's no guarantee in the way SQL Server will execute the query and so once in a while a weird issue like this pops up. I've had it happen before myself. – J.D. Feb 18 '21 at 04:39
  • 1
    Good catch, J.D. I've linked this Q&A to several duplicates on the site. – Josh Darnell Feb 18 '21 at 13:09
  • @JoshDarnell Thanks for the link! I'll read up on it so next time I can articulate the name of this behavior better lol. – J.D. Feb 18 '21 at 15:36