"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() };

This query causes SQL exception to be thrown.

Environment

None

Attachments

1

Activity

Show:

Oskar Berggren May 30, 2012 at 6:31 PM

Closing issues fixed in 3.3.1.CR1.

Oskar Berggren January 22, 2012 at 4:36 PM

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)]

Oskar Berggren November 29, 2011 at 7:07 PM

Small patch with testcase for the first query.

Fixed

Details

Assignee

Reporter

Labels

Components

Fix versions

Affects versions

Priority

Who's Looking?

Open Who's Looking?

Created August 11, 2011 at 6:47 PM
Updated July 8, 2015 at 4:37 AM
Resolved January 22, 2012 at 4:36 PM
Who's Looking?