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

[nmummau]

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

[erikdarlingdata]

Thank you for the report. Your diagnosis was correct. The side check split the whole predicate at its first comparison operator. That put the column from the next comparison on the same side as the CONVERT.

The fix is in #557. It is merged to dev, and it ships in the next release. The check now reads only the comparison that contains the function. It splits the predicate at every AND and OR, also inside parentheses. It does not split inside a string literal or a bracketed name.

The same change removes three related false positives:

• A date range such as [d]>=dateadd(day,(-7),getdate()) AND [d]<getdate().
• ISNULL on the parameter side, such as col = ISNULL(@p, 0). The check still flags col = ISNULL(@p, col), because the column is inside the function.
• A function in a LIKE pattern, such as col like upper(@p).

Your plan is now a test fixture. With the fix, planview analyze reports one warning on it: Scan With Predicate, because the table has no index on A or B.