Patch to allow subqueries with joins using Criteria API and Subqueries with DetachedCriteria -------------------------------------------------------------------------------------------- Key: HHH-952 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-952 Project: Hibernate3 Issue Type: Patch Components: core Affects Versions: 3.1 beta 1, 3.1 beta 2 Environment: 3.1beta1 with MS SQL 2000 via jTDS Reporter: John Priority: Critical Attachments: subquery-patch-311.txt, subquery-patch-313.txt, subquery-patch-31beta3.txt, subquery-patch.txt, subquery-patch.txt, SubqueryExpression.java The existing code in SubqueryExpression.java constructed a select statement but did not have any provisions for creating joins. Therefore, it was not possible using the criteria API to create an exists subselect that had a join, even though running the source DetachedCriteria alone works perfectly. For example, if this is the goal: select * from foo f where exists (select id from bar b join other o on b.o_id = o.id where o.prop = '123' and b.foo_id = f.id) One might try something like this: Criteria crit = session.createCriteria(Foo.class, fooAlias); DetachedCriteria barCrit = DetachedCriteria.forClass(Bar.class, barAlias); DetachedCriteria otherCrit = barCrit.createCriteria(Bar.OTHER_JOIN); otherCrit.add( Restrictions.eq(Other.PROP, "123") ); barCrit.add( Restrictions.eqProperty( -- props to join to foo here --) ); barCrit.setProjection( Projections.id() ); crit.add( Subqueries.exists(barCrit) ); However, the existing code generates something like the following, which gets an error with an unknown alias 'o': select * from foo f where exists (select id from bar b where o.prop = '123' and b.foo_id = f.id) This is also described here (at the end): http://forum.hibernate.org/viewtopic.php?t=942488 The patch to SubqueryExpression.java fixes this to included the joins necessary for the filtering. This code was modeled (copied) off of code from CriteriaLoader. For me this works perfectly, but I don't understand the internals of this stuff enough to say how robust it is. Also included is a patch to the test case to enable testing of this, which was present but commented out. I did not change the contents of the test, which currently only attempts a joined subquery. This used to fail with an error, but now it works. The test does not check the results at all. (Inconsequential to the patch - Enrollment has two Ls.) -----side notes The patch file also has two other patches. The first increases the delay in BulkManipulationTest because I was getting inconsistent test results. I think that the precision on the version timestamp is not enough for 300 milliseconds delay to be enough to guarantee the test results. Also, in build.xml, there was a line that was meant to exclude the performance tests, but there was no **/*, on *, so they actually were not excluded. I changed this so the tests would complete in a reasonable amount of time. However, there is one other issue with testing that I worked around manually. After each test run, two databases (Users and Email) were left in the database. If I did not manually delete these then the number of failures on the next test run was different. This was really confusing until I figured it out because I was trying to make sure all the other testcases still passed with my patch, but even without the patch I was getting different results.