Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:1.0.2.0
[b]Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:MS SQL Server 2005
[b]The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hi,
I am facing a problem generating an HQL query with left outer joins. Will really appreciate if someone can help me get my query right.

I have the following three tables:
atuser(Id, userName)
Adoption_plan(Id, title)
adoption_plan_user(id, role, atuser_fk, adoption_plan_fk)

I am using the following hql query to get a user and and his 0 or more roles on a plan:
select atuser, planroles.role from atuser left outer join fetch AdoptionPlanUsers planRoles left outer join AdoptionPlan plan where atuser.UserName= :userName and plan.id = :planId

am setting username = ‘a’ and plan id = 1. I have given sample data at the end of this mail.

This results in the following SQL query on execution:
select <several column names> from atuser t1
left outer join adoption_plan_user t2
on t1.id = t2.user_fk
left outer join adoption_plan t3
on t2.plan_fk = t3.id
where
t1.user_name = @p0
and t3.id = @p1

this is incorrect!!!
The query that should have been generated is:
select <several column names> from atuser t1
left outer join adoption_plan_user t2
on t1.id = t2.user_fk
left outer join adoption_plan t3
on t2.plan_fk = t3.id
and t3.id = @p1
where
atuser.user_name = @p0


Notice that in the second query, the "and t3.id = @p1" filter condition is with the join clause and not in the "where" clause. In MS SQL server 2005, the 2 queries listed above return different results with the second one giving the correct resultset.
Here is the sample data I have used for the above tables:

Id username
1 A
2 B

Id Title
1 Plan1
2 Plan2

Id Role User_fk Plan_fk
1 Admin 1 2

I have tried the above with criteria queries as well with the same results. Will appreciate if someone can point out what I am missing here.

Regards,
Manish

This is a know issue, look here for the work around.

http://jira.nhibernate.org/browse/NH-514

That workaround does not actually work -- it produces incorrect results. Hibernate 3 has a with keyword that addresses this scenario.

-MT

Indeed this is in my opinion a very serious drawback to the use of NHibernate. I can't find a way around this problem except maybe building a stored procedure and doing it the old way. Given my current application architecture, this really sucks.

Yes, this is a very serious problem. Queries via HQL are crippled until this is fixed. Please vote for the issue in JIRA.

Unfortunately, fixing it requires reworking the entire HQL parser, or more specifically porting the AST (abstract syntax tree) based parser from Hibernate 3.x to NHibernate. It is estimated to be "months" of work.

I am trying to get my employer to contract a skilled NHibnerate contributor to do this port, but of course management is balking at the cost. If other people need this badly enough, perhaps we can split the cost (currently estimated at $25k)

Having an AST-based HQL parser would give many other benefits, such as a means to provide reliable Intellisense in HQL editors, and make it possible to extend an HQL query with the Criteria API. The current inability to do the latter is also a major problem for us ...