I am using Criteria objects to create a query
and sub-query. The generated sql is not
what I intended and I'm a bit perplexed.

I have 4 tables, 3 relevant:

user
project
role
project_role_user

The first three have a PK of 'id' and the
4th is a junction table of the three.

When a user is on a project, an entry is
placed in project_role_user.

The query I want is a list of all users
except for ones on certain projects.

The SQL query is pretty simple:

select * from user u left join project_role_user pru on u.id = pru.user_id
where u.id not in
(select u1.id from user u1 inner join project_role_user pru1
on u1.id = pru1.user_id where pru1.project_id in (1))

In this case, I want all users who are not on project 1.
The list is usually more than one project, hence the in clause.
I need the ProjectRoleUser related obj for later use.

Here is my hibernate code:

Session session = HibernateUtil.getSession();
Criteria userCriteria = session.createCriteria(User.class);
Criteria pruCriteria = userCriteria.createCriteria("projectRoleUsers", Criteria.LEFT_JOIN);

DetachedCriteria excludedUsersCriteria = DetachedCriteria.forClass(User.class)
.setProjection(Property.forName("id"))
.createCriteria("projectRoleUsers")
.add(Property.forName("project").in(excludeProjectList));


userCriteria.add(Property.forName("id").notIn(excludedUsersCriteria));
List<User> userList = userCriteria.list();

session.close();
return userList;

The generated sql (abbreviated) is this:

select * from user this_
left outer join project_role_user projectrol1_ on this_.id=projectrol1_.user_id
where this_.record_status in (?)
and this_.id not in (select this0__.id as y0_ from user this0__ where projectrol1_.project_id in (?))


The problem is, it's joining the user table on the inner query (this0__) with the project_role_user from the outer query. So if
a user is on multiple projects (frequent), the record
still returns.

When I alias the project_role_user table on the inner query, it errors out saying it can't find it.
It continues to look for the outer project_role_user table.

I'm using hibernate 3.2.5 on MySql 5. JDK 1.5_2.
I'm also using Annotations 3.3.0

I would appreciate any insight into what I'm doing wrong.

There is a bug where joins in a subquery aren't built properly. I believe it is fixed in 3.3 (which isn't released yet.)

I'd like to build a snapshot of the 3.3 build to see if it solves this issue I have. I'm looking in the source tree and I'm having a bit of trouble finding where the latest is. I started pulling down everything from the root (http://anonsvn.jboss.org/repos/hibernate/) and after several hours realized I was getting gigs of source.

I'm looking here now: http://fisheye.jboss.org/viewrep/Hibernate/

I would think it's under core/trunk, or maybe core/branches. I'd like to pull down just the core, if possible, and build that.

Thanks very much

I'm not Maven expert, but I don't think you can build h-core module by itself (seperately from cache-ehcache, testing etc - not tools), as it depends on the it's parent. It _may_ be enough to just download that parent pom and put it in the parent directory, or have it installed in your local repo.

But anyway, to build hibernate-core export from

Then go into that directory and type mvn install to get the build installed into your local repo.

Then you can go into the core dir and whenever you want to rebuild it, just type mvn install or mvn package and you'll get your jar.

NB: you will need to have the JBoss repositories in your maven settings.xml and you will also need either maven > 2.0.8 (i think) or a trunk build of maven.
_some_ info here: http://www.hibernate.org/422.html