Uploaded image for project: 'NHibernate [Moved to GitHub]'
  1. NH-2833

"where" clause not working after "group by", generates wrong SQL

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 3.2.0.GA
    • Fix Version/s: 3.3.0.CR1
    • Component/s: Linq Provider
    • Labels:
    • Sprint:

      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.

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                misicn Nikola Misic
              • Votes:
                2 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Who's Looking?