0

I'm losing my mind again.

I have a query that returns three rows. When I include a Where clause that specifies the ID of those rows, it throws an error about invalid length parameter passed to a Left or Substring function.

     SELECT
        P.PodrobnostiAutoID, P.AkcesAutoID
    FROM    dbo.Podrobnosti P
    LEFT JOIN dbo.vwFirstSynonymika S ON P.PodrobnostiAutoID = S.PodrobnostiAutoID
    INNER JOIN  [dbo].[Katalogy] ('Z') Ltrs on Ltrs.EvidenceLetter = P.EvidenceLetter
    INNER JOIN  dbo.Akces A ON P.AkcesAutoID = A.AkcesAutoID
        LEFT JOIN   RO.vwNejnovejsiInventarizaceNeboPresun vwNINP ON P.PodrobnostiAutoID = vwNINP.PodrobnostiAutoID
        LEFT JOIN   dbo.DepSuplik DS ON vwNINP.DepozitarAutoID = DS.DepozitarAutoID
        LEFT JOIN   dbo.TableOfLokalitas tL ON P.LokalitaAutoID = tL.LokalitaAutoID
        LEFT JOIN   dbo.Taxonomy T ON P.TaxonAutoID = T.TaxonAutoID
        LEFT JOIN   dbo.StratigrafieChrono StCh ON P.StratigrafieChronoID = StCh.StratigrafieChronoID
        LEFT JOIN   dbo.StratigrafieLito StL ON P.StratigrafieLitoID = StL.StratigrafieLitoID
        LEFT JOIN   (Select EvidenceLetter, EvidenceNumber, cnt CntPrilohy From [RO].[vwFTDruhaEvidence]) PDE On P.EvidenceLetter = PDE.EvidenceLetter Collate Czech_CI_AS AND P.EvidenceNumber = PDE.EvidenceNumber
        LEFT JOIN   dbo.TableOfTyps tT ON P.TypAutoID = tT.TypAutoID
        LEFT JOIN   dbo.Lidi L ON vwNINP.ClovekAutoID = L.ClovekAutoID
    Where P.PodrobnostiAutoID IN (3171002,3171025,3172058)

it returns these three rows when I leave off the Where clause, but throws the error when I include it.

3171002 20994

3171025 20994

3172058 20994

The IDs are exactly those in the returned set. Does this make any sense to anyone? Some of the joins are to other tables, some are to views or table functions.

David
  • 208,112
  • 36
  • 198
  • 279
Pete Danes
  • 179
  • 1
  • 11
  • We can't guess what your views or functions do. What's certain is that they *do* use substring operations. Try to find which one causes that problem – Panagiotis Kanavos Jul 03 '23 at 14:10
  • You clearly have a call to `SUBSTRING` somewhere, but we can't see your views or functions so cannot help. If the parameters of that are coming from a call to `CHARINDEX` or `PATINDEX` you need to surround that with `NULLIF(` ... `, 0)` to prevent errors – Charlieface Jul 03 '23 at 14:11
  • All those function calls and collation conversions mean that indexes can't be used, causing the query to run slowly, scanning entire tables even when just 3 rows need to be processed. – Panagiotis Kanavos Jul 03 '23 at 14:12
  • Why all those `LEFT JOIN`s when you don't even reference those tables outside of said `JOIN`s? – Thom A Jul 03 '23 at 14:13
  • Yes, they do. And when I comment out the 3rd join from bottom, it executes just fine. But that select in the Join clause ALSO executes just fine by itself, and this query executes just fine, WITH all the join, when I leave off the final Where clause.. THAT is what I don't understand. – Pete Danes Jul 03 '23 at 14:13
  • It's quick enough for my needs, and it's part of a more complex system that has nothing to do with my problem here. I'm trying to figure out why ADDING the Where clause makes it crash. – Pete Danes Jul 03 '23 at 14:16
  • 2
    Because it changes the plan, so perhaps another row which removed by another plan is getting fetched and there's some substring being applied to it. Your query is bad somewhere, so look for the substrings – siggemannen Jul 03 '23 at 14:19
  • Remove the unnecessary stuff (***all*** the `LEFT JOIN`s), do you still get the error? If the error is coming from an unnecessary `JOIN` then getting rid of them solves the problem. If it doesn't you only have three places to check, not 14. *Divide and conquer* and it's *very* easy to divide 11 of the 14 objects from the problem. – Thom A Jul 03 '23 at 14:19
  • Got it - thanks for the steers, mainly to siggemannen. There was a row in the mix that returned a null, and that blew the string functions out of the water. – Pete Danes Jul 03 '23 at 14:25

0 Answers0