Simplification of Count(*) with multiple group by can generate wrong result
- 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
Assessment
This issue has not been assessed yet.