Unexpected query results with UseRelationalNulls and Sum()

[GertArnold]

In .net, see the output of the following statements:

new[] { default(int?), default(int?) }.Max()     // => null
new[] { default(int?), default(int?) }.Sum()     // => 0

And similar statements in SQL (SQL Server) :

DECLARE @numbers TABLE (Number int);
INSERT @numbers values (null), (null);
SELECT MAX(Number) FROM @numbers;     -- => null
SELECT SUM(Number) FROM @numbers;     -- => null

I.e. the database semantics for the Sum function is different than .net's.

Therefore, when using the UseRelationalNulls() option, I'd expect Sum in a LINQ expression to return null when all contributing items are null. However, it doesn't. It returns 0.

Reproducing code (paste in Linqpad as program, using EF8 NuGet for SQL Server and localdb installed.)

#define useRelationalNulls
void Main()
{
    //newDb();
    using var db = getContext(false);
    db.Tests.Sum(t => t.Number).Dump("Sum all, from queryable");
    db.Database.SqlQueryRaw<int?>("SELECT SUM(Number) AS value FROM Tests").AsEnumerable().First().Dump("Sum all, from raw SQL");
    db.Tests.Where(t => t.Number == null).Sum(t => t.Number).Dump("Sum all null, from queryable");
    db.Database.SqlQueryRaw<int?>("SELECT SUM(Number) AS value FROM Tests WHERE Number IS NULL").AsEnumerable().First().Dump("Sum all null, from raw SQL");

    db.Tests.Max(t => t.Number).Dump("Max all, from queryable");
    db.Database.SqlQueryRaw<int?>("SELECT MAX(Number) AS value FROM Tests").AsEnumerable().First().Dump("Max all, from raw SQL");
    db.Tests.Where(t => t.Number == null).Max(t => t.Number).Dump("Max all null, from queryable");
    db.Database.SqlQueryRaw<int?>("SELECT MAX(Number) AS value FROM Tests WHERE Number IS NULL").AsEnumerable().First().Dump("Max all null, from raw SQL");
}

class Test
{
    public int Id { get; set; }
    public int? Number { get; set; }
}

class MyContext : DbContext
{
    private readonly string _connString;
    private readonly bool _log;

    public MyContext(string connectionString, bool log) : base()
    {
        _connString = connectionString;
        _log = log;
    }
    
    public DbSet<Test> Tests { get; set; }

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity<Test>()
            .HasData(new[]
            {
                new Test { Id = 1, Number = 10 },
                new Test { Id = 2, Number = 20 },
                new Test { Id = 3, Number = null },
                new Test { Id = 4, Number = null },
            });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        bool relationalNulls = false;
#if useRelationalNulls
        relationalNulls = true;
#endif
        optionsBuilder.UseSqlServer(_connString, o => o.UseRelationalNulls(relationalNulls));
        if (_log) optionsBuilder.LogTo(s => s.Dump(), LogLevel.Information);
    }
}

void newDb()
{
    using var db = getContext(false);
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
}

MyContext getContext(bool log)
{
    var connectionString = @$"Server=(localDB)\MSSQLLocalDB;database=nullSemantics;Integrated Security=true;MultipleActiveResultSets=true;Encrypt=true;TrustServerCertificate=true;Application Name=Linqpad";
    return new MyContext(connectionString, log);
}

The output of the statements, both with and without UseRelationalNulls:

Method	Result
Sum all, from queryable	30
Sum all, from raw SQL	30
Sum all null, from queryable	0
Sum all null, from raw SQL	null
Max all, from queryable	20
Max all, from raw SQL	20
Max all null, from queryable	null
Max all null, from raw SQL	null

So we see that the difference in semantics between .net and the database (row 3 and 4) is not affected by relational nulls settings. For other aggregate functions like Max the .net and database semantics are always equal, as said above.

The problem is caused by the query translation that always contains SELECT COALESCE(SUM([t].[Number]), 0). I'd expect this COALESCE call to not be added when database null semantics are switched on.

EF Core version: 8.0.7 Database provider: Microsoft.EntityFrameworkCore.SqlServer 2019 Target framework: NET 8.0 Operating system: win 11