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

[Jon Seymour (JIRA)]

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

Jon Seymour commented on HHH-952:
---------------------------------

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.


> 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