Two queries work until compared with EXCEPT statement

• Leo.Miller

  SSChampion

  Points: 13079

  More actions

  August 20, 2026 at 9:45 pm

  #4943417

  I've got multiple databases on 2 SQL 2019 CU32 servers , all using the same schema and code base. The only difference in patch levels is Test has the July Security Update and Prod has the March one. There's a Table Valued function that fails on one database only and only on prod. Of the 2 databases I'm specifically testing, one is on 110 (SQL 2012) compatibility and the code works, the other is on 150 (SQL 2019) and the code fails, although this only started recently. However the database in 110 mode is set to this mode because the client gets the same problem on this database if it's set to 150 mode, but no problem in 110 mode.

  The function has two almost identical select queries linked with an EXCEPT statement as per the code below (obfuscated but correct). Both queries call string splitter functions based on the one written by Jeff Moden and taken from this article:  http://www.sqlservercentral.com/articles/Tally+Table/72993/ These are essentially the same, the one generates a list of included conditions and the other a list of excluded conditions.

  When I run the individual select statements, they both work as expected for all parameters tested and on both databases. However, when I run them together, with the EXCEPT it errors. I've tried various fixes including using CTEs - it still fails, Temp Tables and Table Variables, both of which work, as does replacing the EXCEPT with a LEFT JOIN and testing for NULLs on the right side of the join.

  The error returned is below, note that the string handling is only being done in the call to the string splitter functions which don't return errors when run separately:  fn_StringIncludes(@String) and fn_StringExcludes(@String).

  Msg 537, Level 16, State 2, Line 8

  Invalid length parameter passed to the LEFT or SUBSTRING function.

  The code is:

  DECLARE
      @Client VARCHAR(10) = 'Acme',
      @String VARCHAR(MAX) = 'AC*';  
  
  SELECT DISTINCT Acc_Id, Acc_Code 
  FROM Accounts a
  INNER JOIN Clients c on a.Prime_Client_Id = c.C_ID
  INNER JOIN fn_StringIncludes(@String) S ON
  a.Acc_Code = S.Match
  OR a.Acc_Code LIKE S.Pattern
  OR a.Acc_Code BETWEEN S.LowerBound AND S.UpperBound
  WHERE c.Client = @Client
  EXCEPT
  SELECT Acc_Id, Acc_Code 
  FROM Accounts a
  INNER JOIN Clients c on a.Prime_Client_Id = c.C_ID
  INNER JOIN fn_StringExcludes(@String) S ON
  a.Acc_Code = S.Match
  OR a.Acc_Code LIKE S.Pattern
  OR a.Acc_Code BETWEEN S.LowerBound AND S.UpperBound
  WHERE c.Client = @Client

  It looks like the optimizer is causing an issue in the string splitter, but only when the two select statements are linked with the EXCEPT.

  I've not seen any indication this is a known bug or that there's a fix for it. My work around's solve the problem, but this looks like a bug in the optimizer. with an upgrade in the planning I'm wondering if I'll see the same issue on 2025.

   

  Leo
  Nothing in life is ever so complicated that with a little work it can't be made more complicated.