SR0007 false positive for NULL-safe IS [NOT] DISTINCT FROM predicates
- 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
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