[BUG] Join OR Clause (rule 15): false positive on the dynamic seek plan for an IN list of parameters #558

Component

CLI Tool

Performance Studio Version

1.26.0

Operating System

Windows 11

Describe the Bug

Rule 15 (Join OR Clause) reports "OR in a join predicate… rewrite as separate queries joined with UNION ALL" for a single-table query with no join and no OR: WHERE t.A IN (@p1, @p2).

For an IN list (or ORs) of parameters on an indexed column, SQL Server builds a dynamic seek: Constant Scans produce the parameter values, then Concatenation, TopN Sort, and Merge Interval combine overlapping ranges, and a Nested Loops runs one Index Seek over those ranges. That is the same operator shape rule 15 looks for, in PlanAnalyzer.Node.cs:

// Pattern: Nested Loops → Merge Interval → TopN Sort → [Compute Scalar] → Concatenation → [Compute Scalar] → 2+ Constant Scans
if (!cfg.IsRuleDisabled(15) && node.PhysicalOp == "Concatenation")
{
var constantScanBranches = node.Children.Count(c => c.PhysicalOp == "Constant Scan" || ...);
if (constantScanBranches >= 2 && IsOrExpansionChain(node))

The rule checks only the operator shape. In an OR join expansion, the Constant Scans produce columns of the outer table. In this plan they produce only the parameters [@p1] and [@p2]. The plan is efficient: one Index Seek with 1 execution, returning 2 rows.

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 an index on the filtered column:
   CREATE DATABASE Repro;
   GO
   USE Repro;
   CREATE TABLE dbo.T (Id INT IDENTITY PRIMARY KEY, A INT NOT NULL, B INT NULL);
   CREATE INDEX IX_T_A ON dbo.T (A);
   INSERT dbo.T (A, B)
   SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL FROM sys.all_objects;
2. Run this query and save its actual execution plan (the plan XML is in the collapsed section below):
   EXEC sp_executesql
   N'SELECT t.Id, t.A FROM dbo.T AS t WHERE t.A IN (@p1, @p2)',
   N'@p1 int, @p2 int', @p1 = 10, @p2 = 20;
   The plan is: Nested Loops (Inner Join), Merge Interval, TopN Sort, Compute Scalar, Concatenation, two Compute Scalar / Constant Scan branches that produce [@p1] and [@p2], and an Index Seek on IX_T_A.
3. Run planview analyze on the saved plan.

Expected Behavior

No Join OR Clause warning. The query has no join and no OR between join columns, and the plan is the efficient dynamic seek for a parameter list: one Index Seek, 1 execution, 2 rows. Rewriting it as UNION ALL wouldn't help.

A way to tell the two apart: in an OR join expansion, the Constant Scan (or Compute Scalar) values reference columns of another input ([db].[schema].[table].[col]). In the dynamic seek for a parameter or literal list, they reference only parameters, variables or literals, such as [@p1], [@p2], (62). Flagging only the first case would keep the real warning and drop this one.

Actual Behavior

Warning | Join OR Clause | Concatenation (Node 4)
OR in a join predicate. SQL Server rewrote the OR as 2 separate lookups, each evaluated independently — this multiplies the work on the inner side. Rewrite as separate queries joined with UNION ALL. For example, change "FROM a JOIN b ON a.x = b.x OR a.y = b.y" to "FROM a JOIN b ON a.x = b.x UNION ALL FROM a JOIN b ON a.y = b.y".

Plan File

Screenshots

No response