Misplaced variable marking in a 'having' clause

rabowers · **Posted:** Sun Jul 09, 2006 8:22 am

NHibernate version: 1.0.2

Hello all.

I'm seeing what looks like a misplaced SQL '?' parameter from a variable placed in the 'having' clause of an HQL query.

In its simplest form, the HQL query is:

Code:

select o.sender.id from Order o group by o.sender having count(o) >= :orderCount

Where there are two tables, OrderTable mapped as the persistent object Order and Address mapped as Address. o.sender is an instance of Address, and is mapped as a uni-directional many-to-one from Order to Address thus:

Code:

<many-to-one name="sender" access="field" fetch="select" column="senderaddrid" not-null="true" />

So the query is trying to return the ids of Address objects that orderCount or more instances of Order existing.

Anyway, the problem is that the SQL generated by NHibernate comes out as:

Code:

select order0_.senderaddrid as x0_0_ from OrderTable order0_ where ? group by  order0_.senderaddrid having (count(order0_.orderid)>=)

instead of the expected:

Code:

select order0_.senderaddrid as x0_0_ from OrderTable order0_ group by  order0_.senderaddrid having (count(order0_.orderid)>=?)

which does run as expected in the Access database at the back end.

If I put a dummy 'where' clause into the HQL like this:

Code:

select o.sender.id from Order o where o.id <> 0 group by o.sender having count(o) >= :orderCount

it still comes out like this:

Code:

select order0_.senderaddrid as x0_0_ from OrderTable order0_ where (order0_.orderid<>0)? group by  order0_.senderaddrid having (count(order0_.orderid)>=)

(I've also tried making the 'group by' be on o.sender.id instead, but the end result is the same.)

Can anyone shed any light on this? The documentation gives an example of using aggregate functions in the having clause, but only with a constant and not a variable. Am I approaching the problem from the wrong way round - should I be selecting from the Address table and putting a one-to-many link from Address to Order (which is uni-directional from the other way at the moment)?

Many thanks,
Richard.

onevextchuck · **Posted:** Thu Jan 18, 2007 6:06 pm

Hello,

I am curious if there was ever a solution for this. Please tell me I just don't understand HQL. We are currently running into the exact same problem. We are now using NHibernate 1.2 Beta 3.

The example queries follow at the end of the post, but the important point to note is that it looks like our named parameter ":Integer1" is simply being removed from the having clause and "where ?" is getting added in.

We have tried modifying the code and found that the named parameter seems to be part of the problem.
* If the named parameter is removed and replaced with a constant, everything works fine, no "where ?" is added.
* If we create a dummy where clause, "where 1=1", ":Interger1" is removed and the where clause ends up being "where 1=1?".

We have stepped through some of the NHibernate code and have found that when the token :Integer1 is parsed, NHibernate is adding a ? token to the WhereTokens collection of the QueryTranslator. The HavingTokens collection simply remains {"(", "count", "(", "comments.id", ")", ">", ")" }. My concern is that the ? from the WhereTokens collection should probably be between the > and ) in the HavingTokens collection. We will try making that change in memory and see if the query is built correctly next.

We will continue digging to see what the problem is, but if anyone could save us some time. I would love to hear a simple "You're using NHibernate wrong!" at this point because I have definately learned more than enough about the innards of NHibernate for one day. =)

Our HQL query looks like this:

Code:

select doc1.Id 
from Document as doc1 
    left join doc1.CommentCollection as comments 
group by doc1.Id 
having count(comments.Id) > :Integer1

The SQL query that we get back as a result is:

Code:

select 
    document0_.asset_id as x00 
from Shamu.dbo.[Document] document0_ 
    inner join Shamu.dbo.[Asset] document01 on document0_.asset_id=document0_1_.taggable_thing_id 
    inner join Shamu.dbo.[Taggable_Thing] document02 on document0_.asset_id=document0_2_.thing_id 
    inner join Shamu.dbo.[Thing] document03 on document0_.asset_id=document0_3_.id 
    left outer join Shamu.dbo.[Comment] commentcol1_ on document0_.asset_id=commentcol1_.taggable_thing_id 
    left outer join Shamu.dbo.[Thing] commentcol11 on commentcol1_.thing_id=commentcol1_1_.id 
where ? 
group by  document0_.asset_id 
having (count(commentcol1_.thing_id)>) 

Sorry for the verbose examples.

onevextchuck · **Posted:** Thu Jan 18, 2007 10:36 pm

I spent the better part of an evening poking around in the NHibernate source and for the most part it looks like support for named parameters in the having clause was just never fully implemented. The reason that the parameter placement was getting moved to the where clause is that in the NHibernate model, the having clause inherits from the where clause and the method to add a parameter was never overriden in the having clause object. This leads to the where clause implementation being called which then adds the parameter to the where clause instead of the having clause.

It does look like the fix is fairly simple though. I modified three files a bit and everything started working for us. Also our 200 unit tests (it's still a small application) kept working so I'm fairly comfortable with the change. If you're still interested in a fix, I can send you the files or our version of the dll. I will try submitting the changes to JIRA to get them into the build.