Invalid SQL generated when using Null Coalescing Operator on bool for the SQLServer provider

[alaatm]

Given the following

public class Vehicle
{
    public int Id { get; set; }
    public VehicleRegistration Registration { get; set; } = default!;
}
public class VehicleRegistration
{
    public int Id { get; set; }
    public bool? Approved { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Vehicle> Vehicles { get; set; } = default!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder
        //.UseSqlite("Data Source=tst.db;");
        .UseSqlServer(@"Server=.;Database=EFCoreIssueNullableBool;Trusted_Connection=True;MultipleActiveResultSets=true");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Vehicle>()
            .HasOne(p => p.Registration)
            .WithOne()
            .HasForeignKey<VehicleRegistration>("vehicle_id")
            .IsRequired();
    }
}

When I execute the following query:

db.Vehicles.Where(p => !p.Registration.Approved ?? true);

The generated sql is wrong:

SELECT [v].[Id]
FROM [Vehicles] AS [v]
LEFT JOIN [VehicleRegistration] AS [v0] ON [v].[Id] = [v0].[vehicle_id]
WHERE COALESCE(CASE
    WHEN [v0].[Approved] = CAST(0 AS bit) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, CAST(1 AS bit)) = CAST(1 AS bit)

The second argument of the COALESCE function will never be picked even if [v0].[Approved] is null due to the CASE WHEN ... THEN ... ELSE ... END statement always evaluating to a non-null value.

The Sqlite provider generates a good sql that works:

SELECT "v"."Id"
FROM "Vehicles" AS "v"
LEFT JOIN "VehicleRegistration" AS "v0" ON "v"."Id" = "v0"."vehicle_id"
WHERE COALESCE(NOT ("v0"."Approved"), 1)

As a workaround, the query can be written as:

db.Vehicles.Where(p => p.Registration.Approved == null || !p.Registration.Approved.Value));

Where the following good sql is produced:

SELECT [v].[Id]
FROM [Vehicles] AS [v]
LEFT JOIN [VehicleRegistration] AS [v0] ON [v].[Id] = [v0].[vehicle_id]
WHERE ([v0].[Approved] IS NULL) OR ([v0].[Approved] = CAST(0 AS bit))

A working runnable repro is available here: https://github.com/alaatm/EFCoreIssueNullableBool

EF Core version: 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: (e.g. .NET 6.0.1)

[alaatm]

Interestingly, this works too:

db.Vehicles.Where(p => !(p.Registration.Approved ?? false))

generating:

SELECT [v].[Id]
FROM [Vehicles] AS [v]
LEFT JOIN [VehicleRegistration] AS [v0] ON [v].[Id] = [v0].[vehicle_id]
WHERE COALESCE([v0].[Approved], CAST(0 AS bit)) = CAST(0 AS bit)

[smitpatel]

We don't really process !NULL as possible NULL we try to convert it to 2-value logic before applying ! operator. @maumar will have better idea how null semantics interact with this structure.

[edmacdonald]

I ran into this issue today, almost 2 years and a major version later (v7.0.11). If fixing it is not a priority, the framework should at least fail spectacularly when this syntax is used to avoid insidious, hard to find bugs.

[ranma42]

This seems to be a special case of #34001