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

[John (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27091 ] 

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

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.

> 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-3.2.4.SP1.txt, 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.

-- 
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