Incorrect SUBSTRING usage in debug section in sp_BlitzCache and sp_BlitzIndex

[kiloscheffer]

Version of the script
8.17

What is the current behavior?
sp_BlitzCache and sp_BlitzIndex have a section like this:

IF @Debug = 1
BEGIN
    PRINT SUBSTRING(@sql, 0, 4000);
    PRINT SUBSTRING(@sql, 4000, 8000);
    PRINT SUBSTRING(@sql, 8000, 12000);
    PRINT SUBSTRING(@sql, 12000, 16000);
    PRINT SUBSTRING(@sql, 16000, 20000);
    PRINT SUBSTRING(@sql, 20000, 24000);
    PRINT SUBSTRING(@sql, 24000, 28000);
    PRINT SUBSTRING(@sql, 28000, 32000);
    PRINT SUBSTRING(@sql, 32000, 36000);
    PRINT SUBSTRING(@sql, 36000, 40000);
END;

What is the expected behavior?
The syntax for SUBSTRING is

SUBSTRING ( expression, start, length )

and the numbering of start is 1-based, so the section should probably be

IF @Debug = 1
BEGIN
    PRINT SUBSTRING(@sql, 1, 4000);
    PRINT SUBSTRING(@sql, 4001, 4000);
    PRINT SUBSTRING(@sql, 8001, 4000);
    PRINT SUBSTRING(@sql, 12001, 4000);
    PRINT SUBSTRING(@sql, 16001, 4000);
    PRINT SUBSTRING(@sql, 20001, 4000);
    PRINT SUBSTRING(@sql, 24001, 4000);
    PRINT SUBSTRING(@sql, 28001, 4000);
    PRINT SUBSTRING(@sql, 32001, 4000);
    PRINT SUBSTRING(@sql, 36001, 4000);
END;

[BrentOzar]

Can you tell me a little about the problem this is causing for you on a production server?---Tiny glass keyboardTypos flowing like riversAs winter snow thawsOn Dec 11, 2023, at 3:27 AM, Per Scheffer ***@***.***> wrote: Version of the script 8.17 What is the current behavior? sp_BlitzCache and sp_BlitzIndex have a section like this: IF @debug = 1 BEGIN PRINT ***@***.***, 0, 4000); PRINT ***@***.***, 4000, 8000); PRINT ***@***.***, 8000, 12000); PRINT ***@***.***, 12000, 16000); PRINT ***@***.***, 16000, 20000); PRINT ***@***.***, 20000, 24000); PRINT ***@***.***, 24000, 28000); PRINT ***@***.***, 28000, 32000); PRINT ***@***.***, 32000, 36000); PRINT ***@***.***, 36000, 40000); END; What is the expected behavior? The syntax for SUBSTRING is SUBSTRING ( expression, start, length ) and the numbering of start is 1-based, so the section should probably be IF @debug = 1 BEGIN PRINT ***@***.***, 1, 4000); PRINT ***@***.***, 4001, 4000); PRINT ***@***.***, 8001, 4000); PRINT ***@***.***, 12001, 4000); PRINT ***@***.***, 16001, 4000); PRINT ***@***.***, 20001, 4000); PRINT ***@***.***, 24001, 4000); PRINT ***@***.***, 28001, 4000); PRINT ***@***.***, 32001, 4000); PRINT ***@***.***, 36001, 4000); END; —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[kiloscheffer]

It's causing no problems. I just stumbled upon this code while searching for something else. But the debug output would be weird.

Here is an example of similar code:

DECLARE @string nvarchar(10) = N'1234567890';

PRINT SUBSTRING(@string, 0, 2);
PRINT SUBSTRING(@string, 2, 4);
PRINT SUBSTRING(@string, 4, 6);
PRINT SUBSTRING(@string, 6, 8);
PRINT SUBSTRING(@string, 8, 10);

which outputs

1
2345
456789
67890
890

What is intended here is probably:

DECLARE @string nvarchar(10) = N'1234567890';

PRINT SUBSTRING(@string, 1, 2);
PRINT SUBSTRING(@string, 3, 2);
PRINT SUBSTRING(@string, 5, 2);
PRINT SUBSTRING(@string, 7, 2);
PRINT SUBSTRING(@string, 9, 2);

which outputs

12
34
56
78
90

[kiloscheffer]

It is possible that a pull request would have been more suitable in this instance rather than a bug report; however, my familiarity with GitHub is still limited.

[Montro1981]

@perscheffer Thanks for the report, I added PR #3407 to fix this issue.

[BrentOzar]

Looks good! Thanks for the pull request. Merging into the dev branch, will be in the next release with credit to you in the release notes.