"where" clause not working after "group by", generates wrong SQL
Description
var data = from p in session.Query<Product>() group p by p.Category.CategoryName into g where g.Count() >= 10 select new { Name = g.Key, Count = g.Count() };
This query returns wrong result. If a total number of products is greater that 10 it will return all categories, not just those having more than 10 products. Here is SQL being generated:
exec sp_executesql N'select category1_.CategoryName as col_0_0_, cast(count as INT) as col_1_0_ from Products product0_ left outer join Categories category1_ on product0_.CategoryID=category1_.CategoryID where (select cast(count as INT) from Products product0_)>@p0 group by category1_.CategoryName',N'@p0 int',@p0=10
One can clearly see that in subquery it's counting all products, instead just products in given category.
var data = from p in session.Query<Product>() group p by p.Category.CategoryName into g where g.Key.StartsWith("B") select new { Name = g.Key, Count = g.Count() };
Fix from Alexander merged in 37dc29e84d13ce5b733b2bc874d3fe8c5837eecb.
Oskar Berggren January 18, 2012 at 8:12 PM
Or the SQL can use a subselect.
Oskar Berggren November 29, 2011 at 7:09 PM
The expected SQL for the first query is: select customer1_.CompanyName as col_0_0_, cast(count as INT) as col_1_0_ from Orders order0_ left outer join Customers customer1_ on order0_.CustomerId=customer1_.CustomerId group by customer1_.CompanyName having cast(count as INT) > @p0 @p0 = 10 [Type: Int32 (0)]
var data = from p in session.Query<Product>()
group p by p.Category.CategoryName into g
where g.Count() >= 10
select new { Name = g.Key, Count = g.Count() };
This query returns wrong result. If a total number of products is greater that 10 it will return all categories, not just those having more than 10 products.
Here is SQL being generated:
exec sp_executesql N'select category1_.CategoryName as col_0_0_, cast(count as INT) as col_1_0_ from Products product0_ left outer join Categories category1_ on product0_.CategoryID=category1_.CategoryID where (select cast(count as INT) from Products product0_)>@p0 group by category1_.CategoryName',N'@p0 int',@p0=10
One can clearly see that in subquery it's counting all products, instead just products in given category.
var data = from p in session.Query<Product>()
group p by p.Category.CategoryName into g
where g.Key.StartsWith("B")
select new { Name = g.Key, Count = g.Count() };
This query causes SQL exception to be thrown.