[BUG] Non-SARGable Predicate (rule 12): false positive when a parameter-side function follows another comparison in a compound predicate #556

Component

CLI Tool

Performance Studio Version

1.26.0

Operating System

Windows 11

Describe the Bug

Rule 12 (Non-SARGable Predicate) reports Function call (CONVERT) on column when the CONVERT wraps a parameter, not a column. It happens when the predicate has more than one comparison joined by AND or OR.

IsFunctionOnColumnSide in PlanAnalyzer.Detection.cs finds the first comparison operator in the whole predicate and splits there. It then checks whether the side with the function contains a column reference:

var compMatch = Regex.Match(predicate, @"(?<![<>])([<>=!]{1,2})(?![<>=])");
...
string side = funcSide == "left" ? predicate[..compPos] : predicate[(compPos + compMatch.Length)..];
return ColumnReferenceRegex.IsMatch(side);

In the predicate below, the first = is after [t].[A]. So the "function side" of CONVERT(tinyint,[@2],0) is everything to the right of that first =, which includes the second condition's column [t].[B]. The column check matches, and the function is treated as column-side. #437 (for #436) fixed the equivalent case for CONVERT_IMPLICIT, but IsFunctionOnColumnSide still uses one split point for the whole predicate.

Version: v1.26.0 (e6593df), planview analyze with the default rules, on SQL Server 2022 (16.0.4195.2).

Steps to Reproduce

1. Create a table with no index on the filtered columns:
   CREATE DATABASE Repro;
   GO
   USE Repro;
   CREATE TABLE dbo.T (Id INT IDENTITY PRIMARY KEY, A INT NOT NULL, B TINYINT NOT NULL);
   INSERT dbo.T (A, B) VALUES (52, 2), (53, 1);
2. Run this query and save its actual execution plan (attached as non-sargable-compound-predicate.sqlplan):
   SELECT COUNT() FROM dbo.T AS t WHERE t.A = 52 AND t.B = CONVERT(tinyint, 2);
   SQL Server auto-parameterizes it as (@1 tinyint,@2 int)SELECT COUNT() FROM [dbo].[T] [t] WHERE [t].[A]=@1 AND [t].[B]=CONVERT([tinyint],@2). The plan is a Clustered Index Scan with this predicate:
   [Repro].[dbo].[T].[A] as [t].[A]=CONVERT_IMPLICIT(int,[@1],0) AND [Repro].[dbo].[T].[B] as [t].[B]=CONVERT(tinyint,[@2],0)
3. Run planview analyze non-sargable-compound-predicate.sqlplan.

Expected Behavior

No Non-SARGable Predicate warning. CONVERT_IMPLICIT(int,[@1],0) and CONVERT(tinyint,[@2],0) both wrap parameters, and the columns [t].[A] and [t].[B] are compared without any function around them, so the predicate is SARGable. (The scan in this repro happens because there's no index on A or B, not because of the predicate.)

Each function should be checked against its own comparison. For example, split the predicate at top-level AND/OR first, then find the comparison operator within the part that contains the function. A test with a two-condition predicate, where only the second condition has a parameter-side CONVERT, would cover this.

Actual Behavior

planview reports a Non-SARGable Predicate warning on the scan:

Warning | Non-SARGable Predicate | Clustered Index Scan on Repro.dbo.T (Node 2)
Function call (CONVERT) on column prevents an index seek. Remove the function from the column side — apply it to the parameter instead, or create a computed column with the expression and index that.
Predicate: [Repro].[dbo].[T].[A] as [t].[A]=CONVERT_IMPLICIT(int,[@1],0) AND [Repro].[dbo].[T].[B] as [t].[B]=CONVERT(tinyint,[@2],0)

Plan File

Screenshots

No response