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

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

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-952?page=comments#action_19807 ] 

John commented on HHH-952:
--------------------------

I should also say that this is a patch from the v31beta1 tag, but that SubqueryExpression is the same version in HEAD.

> 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