hibernate-issues

[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

[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_19806 ] 

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

Sorry, in the middle of the last paragraph it should be 
"but there was no **/*, ONLY *, so"

> 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

[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

[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

[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: subquery-patch.txt

Patch version of changes

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

[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_19832 ] 

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

Oops - new patch has the same name.  Big one is newer (larger id, too).

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

[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: subquery-patch-31beta3.txt

I merged this into 3.1beta3 and everything still seems fine.  The previous change to BulkManipulationTest was fixed already by someone else.  However, the performance test patch is still in build.xml.  

> 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-31beta3.txt, subquery-patch.txt, 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

[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_20248 ] 

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

Any comments on this?  I'm assuming this isn't on the table for 3.1.  If not, I'll wait until 3.1 is finalized to update the patch.

> 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-31beta3.txt, subquery-patch.txt, 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

[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: subquery-patch-311.txt

Diff versus 3.1.1 tag.  The file didn't change since beta3, but I redid the patch anyway.

> 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-311.txt, subquery-patch-31beta3.txt, subquery-patch.txt, 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

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

[Max R. A. (JIRA) <no...@at...>]

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

Max Rydahl Andersen updated HHH-952:
------------------------------------

    Priority: Critical  (was: Minor)

We should try and apply this patch since it greatly limits the use of SubCritiria with joins.


> 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: Critical
>  Attachments: SubqueryExpression.java, subquery-patch-311.txt, subquery-patch-31beta3.txt, subquery-patch.txt, 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

[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_22748 ] 

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

The move to Subversion screwed me up a little, but this file hasn't changed in v313, so the patch should be valid.  I haven't messed with 3.2 yet.  Let me know if you need anything.

> 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: Critical
>  Attachments: SubqueryExpression.java, subquery-patch-311.txt, subquery-patch-31beta3.txt, subquery-patch.txt, 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

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

[Erik K. (JIRA) <no...@at...>]

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

Erik Kimmel commented on HHH-952:
---------------------------------

I tested the patch with v313 and all seems to be OK.

> 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: Critical
>  Attachments: SubqueryExpression.java, subquery-patch-311.txt, subquery-patch-31beta3.txt, subquery-patch.txt, 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

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

[Thomas K. (JIRA) <no...@at...>]

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

Thomas Klaeger updated HHH-952:
-------------------------------

    Attachment: subquery-patch-313.txt

Updated for subversion, Hibernate 3.1.3, unified diff

> 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: Critical
>  Attachments: SubqueryExpression.java, subquery-patch-311.txt, subquery-patch-313.txt, subquery-patch-31beta3.txt, subquery-patch.txt, 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