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.)
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.
3.1beta1 with MS SQL 2000 via jTDS
I've attached a variant (subquery-patch-3.2.4.SP1.txt) of (subquery-patch-313.txt) which addresses the NullPointerException that I reported with a previous post.
It seems that earlier versions of the patch were relying on constructor and initializer behaviour that is not guaranteed by the Java Language Specification (in particular, the order in which subclass instance variables and inner class references are initialized w.r.t. to the execution of base class constructors). This patch addresses this by removing the use of the inner class and adding an explicit alias argument to the constructors in the hierarchy of CriteriaJoinWalker.
Maybe something changed in the related logic - I didn't knowingly get tricky with the constructor order. Mostly I took code from other places in Hibernate that were doing the right thing and cobbled together something that works. The criteria code is pretty complex and this was my first use of Hibernate, and being in the middle of a project I only had time enough to spend solving my problem. I'm glad it did and just hoped this patch would get into the eyes of someone with a lot more knowledge of it than me. Thanks everyone for keeping the patch updated with newer Hibernate versions. I haven't had time and our app isn't being updated, so I appreciate everyone keeping it up.
Code committed is the 3.2.4.SP1 patch almost verbatim.
Commited fiix to 3.2 branch and made formatting corrections (e.g., spaces to tabs) to trunk version.
Is there some place that shows how to apply these patches to source files? All the other delta's seem to use a different format then the top one for 3.2.4.sp1.