dotnet / dotnet/efcore

Simplification of Count(*) with multiple group by can generate wrong result

Open
#27,130 2 comments 0 reactions 0 assignees View on GitHub
area-groupby area-query customer-reported priority-bug
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

```C#
ss.Set().GroupBy(e => e.CustomerID)
.Select(g => new
{
g.Key,
A = ss.Set().Where(e => e.City == "Seattle").GroupBy(e => e.City)
.Select(g2 => g2.Count() + g.Min(e => e.OrderID))
.OrderBy(e => 1)
.FirstOrDefault()
})
```
Generates
```SQL
SELECT [o].[CustomerID] AS [Key], COALESCE((
SELECT TOP(1) COUNT(*) + MIN([o].[OrderID])
FROM [Employees] AS [e]
WHERE [e].[City] = N'Seattle'
GROUP BY [e].[City]
ORDER BY (SELECT 1)), 0) AS [A]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerID]
```
Which works correctly.
But if the `g.Min(e => e.OrderID)` is converted to `g.Count()` then generate the SQL like this
```SQL
SELECT [o].[CustomerID] AS [Key], COALESCE((
SELECT TOP(1) COUNT(*) + COUNT(*)
FROM [Employees] AS [e]
WHERE [e].[City] = N'Seattle'
GROUP BY [e].[City]
ORDER BY (SELECT 1)), 0) AS [A]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerID]
```
Now all of sudden the aggregate which is supposed to count records on outer grouping is computing that on inner one producing wrong results.

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.