Unexpected query results with UseRelationalNulls and Sum()
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
In .net, see the output of the following statements:
```c#
new[] { default(int?), default(int?) }.Max() // => null
new[] { default(int?), default(int?) }.Sum() // => 0
```
And similar statements in SQL (SQL Server) :
```sql
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.)
```c#
#define useRelationalNulls
void Main()
{
//newDb();
using var db = getContext(false);
db.Tests.Sum(t => t.Number).Dump("Sum all, from queryable");
db.Database.SqlQueryRaw("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("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("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("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 Tests { get; set; }
protected override void OnModelCreating(ModelBuilder mb)
{
mb.Entity()
.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
Contributor guide
Assessment
This issue has not been assessed yet.