[Hibernate-issues] [Hibernate-JIRA] Created: (HHH-952) Patch to allow subqueries with joins using Criteria API and Subqueries with DetachedCriteria

[John (JIRA) <no...@at...>]

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
        Type: Patch
  Components: core  
    Versions: 3.1 beta 1, 3.1 beta 2    
 Environment: 3.1beta1 with MS SQL 2000 via jTDS
 Reporter: John
    Priority: Minor
 Attachments: subquery-patch.txt

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.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira