microsoft / microsoft/DacFx

SR0007 false positive for NULL-safe IS [NOT] DISTINCT FROM predicates

Open
#825 0 comments 0 reactions 0 assignees View on GitHub
area: code analysis bug
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

- Microsoft.Build.Sql SDK 2.2.0 and SQL Database Projects extension 1.6.2 are also installed
- .NET Framework (Windows-only) or .NET Core: .NET Core / `dotnet build`
- Environment (local platform and source/target platforms): Windows local environment; source project targets SQL Server 2022; staging project targets SQL Server 2022

**Steps to Reproduce:**

1. Create a SQL database project using the AdventureworksLT2012 sample database
2. Change the project Target Platform to SQL Server 2022+
3. Add the following stored procedure that make use of the NULL-safe `IS NOT DISTINCT FROM` operator:

```sql
CREATE PROCEDURE [dbo].[usp_TEST_SR0007]
AS
SELECT p1.[ProductID]
FROM [SalesLT].[Product] p1
INNER JOIN [SalesLT].[Product] p2 ON p2.[ProductID] = p1.[ProductID]
WHERE p1.[Color] IS NOT DISTINCT FROM p2.[Color]
GO
```

4. Build with SQL static code analysis enabled:

```text
dotnet build AdventureworksLT.sqlproj /p:RunSqlCodeAnalysis=true
```

5. Observe warning `SR0007`:

```text
Microsoft.Rules.Data : Nullable columns can cause final results to be evaluated as NULL for the predicate.
```

`IS DISTINCT FROM` is explicitly NULL-safe:

```text
NULL IS DISTINCT FROM NULL = false
NULL IS DISTINCT FROM 'value' = true
'value' IS DISTINCT FROM NULL = true
```

The warning appears to be a false positive because the predicate cannot evaluate to `UNKNOWN` due to the operator's NULL-safe semantics.

**Did this occur in prior versions? If not - which version(s) did it work in?**

Not tested in prior versions. The project builds successfully, but SQL static code analysis reports `SR0007` for these predicates.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

**Likely cause:**

`IS [NOT] DISTINCT FROM` only arrived in SQL Server 2022, and SR0007's nullable-column check almost certainly predates it. It sees a nullable column in a predicate and flags it without recognising the operator as NULL-safe.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing SR0007 with the AdventureworksLT.sqlproj command shown and the [dbo].[usp_TEST_SR0007] procedure. Trace the SQL static analysis path for IS [NOT] DISTINCT FROM predicates and compare it with existing nullable-predicate handling. Done means the NULL-safe examples no longer emit SR0007 while ordinary nullable predicates still do.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.