[Hibernate-issues] [Hibernate-JIRA] Updated: (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=all ]

John updated HHH-952:
---------------------

    Attachment: SubqueryExpression.java

I have included the whole SubqueryExpression file as a new update.  This file contains additional changes over what is included in the prior patch.  I fixed a couple of issues that came up after I tried to perform an even more complicated query.  Though mine was a little more involved, I think the following example is illustrative:

select * from foo f
where exists (
  select b.id from bar b
  where b.x = '2' and b.ndx = (select max(b2.ndx) from bar b2 where b2.fid = b.fid)
)
(Yes, there are other ways to structure this, but this is how I wanted to do it.)

Anyway, there were a couple of problems that prevented this from working.  Firstly, I was getting NullPointerExceptions because the params field of the object wasn't set when getTypedValues was called on the second inner query because toSqlString was never called first.  After that was fixed, the second inner query was using the same alias as the outer one, so the join didn't work correctly.  To fix the first issue I extracted the creation of the innerQuery so it could be called from either method, so toSqlString being called is no longer a prerequisite for calling getTypedValues.  Secondly, I used the alias of the criteria as the alias for the innerQuery, instead of generating one, so that it will be unique assuming proper setup by the user when creating the query.  In this type of situation that is a requirement anyway for the thing to work at all, so I don't see that as a problem.  I also renamed a variable in getTypedValues so it didn't shadow a member field.

A note on the implementation: see the comments in extractSessionFactoryImplementor for potential gotchas about this method.  I removed the cast to CriteriaImpl (which was already marked as ugly) for two reasons: sometimes the criteria was a CriteriaImpl$Subcriteria, and sometimes it didn't have a SessionImplementor set.  To get around this I made the assumption that all things within the same query would have the same SessionFactoryImplementor, which I get directly from the criteriaQuery.

As mentioned before, I ensured that all tests still pass that passed before, and this works wonderfully for me, but I don't have enough Hibernate knowledge to forsee other areas of impact.

> 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: SubqueryExpression.java, 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