dotnet / dotnet/efcore

Group by in subquery produces wrong result when the source is empty

Open
#32,909 0 comments 3 reactions 0 assignees View on GitHub
area-groupby area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

A subquery with a group by and DefaultIfEmpty produces different results depending on where it is in the query

Repro code:
```C#
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

using var db = new AppDb();

var q1 = from t in db.Table.DefaultIfEmpty()
group t by t != null into g
select new
{
Count = g.Count(),
Sum = g.Sum(t => t.Value),
};

var q2 = from a in db.Dummy
from b in q1 // dummy cross join q1
select new
{
b.Count,
b.Sum,
};

var q3 = from b in q1 // q1 cross join dummy
from a in db.Dummy
select new
{
b.Count,
b.Sum,
};

Console.WriteLine(q1.ToQueryString());
Console.WriteLine();
Console.WriteLine(q2.ToQueryString());
Console.WriteLine();
Console.WriteLine(q3.ToQueryString());

class AppDb : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder builder)
=> builder.UseSqlServer("Data Source=localhost;Initial Catalog=Test;Integrated Security=true");
public DbSet Dummy => this.Set();
public DbSet Table => this.Set();
}
record class Dummy(int Id);
record class Stuff(int Id, int Value);
```

SQL dump:
```sql
SELECT COUNT(CASE
WHEN [t0].[Id] IS NOT NULL THEN 1
END) AS [Count], COALESCE(SUM([t0].[Value]), 0) AS [Sum]
FROM (
SELECT [t].[Id], [t].[Value], CASE
WHEN [t].[Id] IS NOT NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [Key]
FROM (
SELECT NULL AS [empty]
) AS [e]
LEFT JOIN [Table] AS [t] ON 1 = 1
) AS [t0]
GROUP BY [t0].[Key] -- grouping covers the DefaultIfEmpty extent

SELECT [t2].[Count], [t2].[Sum]
FROM [Dummy] AS [d]
CROSS JOIN (
SELECT [t1].[Count], [t1].[Sum]
FROM (
SELECT NULL AS [empty]
) AS [e]
LEFT JOIN (
SELECT COUNT(1) AS [Count], COALESCE(SUM([t0].[Value]), 0) AS [Sum]
FROM (
SELECT [t].[Id], [t].[Value], CAST(1 AS bit) AS [Key]
FROM [Table] AS [t]
) AS [t0]
GROUP BY [t0].[Key] -- grouping DOES NOT cover the DefaultIfEmpty extent
) AS [t1] ON 1 = 1
) AS [t2]

SELECT [t1].[Count], [t1].[Sum]
FROM (
SELECT COUNT(CASE
WHEN [t0].[Id] IS NOT NULL THEN 1
END) AS [Count], COALESCE(SUM([t0].[Value]), 0) AS [Sum]
FROM (
SELECT [t].[Id], [t].[Value], CASE
WHEN [t].[Id] IS NOT NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [Key]
FROM (
SELECT NULL AS [empty]
) AS [e]
LEFT JOIN [Table] AS [t] ON 1 = 1
) AS [t0]
GROUP BY [t0].[Key] -- grouping covers the DefaultIfEmpty extent
) AS [t1]
CROSS JOIN [Dummy] AS [d]
```

Assuming a single row in [Dummy] and an empty [Table] the queries return the following results:
```
0 0
NULL NULL <-- this is wrong, it should be 0 0
0 0

```

### provider and version information

EF Core version: 8.0.1 (also repros w/ 7.0 and 6.0, unsupported/crash on <6.0)
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 8.0

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.