2 Replies Latest reply on Oct 7, 2005 3:17 PM by dsouza

Subquery problem

dsouza Oct 6, 2005 3:54 PM

Hi, I'm trying to make a EBJ QL query with a subquery but it seems the final database query generated is coming out wrong.

It's quite a simple query. I have an entity bean called ProductClass with a bi-directional relationship to itself (children and parents). I'd like to retrieve all objects with no parents, so I created the query:

select child from ProductClass as child where not exists (from ProductClass as parent where parent.children = child)

This is what is generated for Oracle to handle:

select productcla0_.id as id149_, productcla0_.NAME as NAME149_ from JBOSS.PRDCT_CLASS productcla0_ where not (exists (select productcla1_.id from JBOSS.PRDCT_CLASS productcla1_, JBOSS.PRDCT_CLASS_TO_CHILD children2_, JBOSS.PRDCT_CLASS productcla3_ where productcla1_.id=children2_.PARENT_PRDCT_CLASS_ID and children2_.CHILD_PRDCT_CLASS_ID=productcla3_.id and .=productcla0_.id))

Note that the last where clause says " .=productcla0_.id" where it should say "productcla3_.id=productcla0_.id" to complete the join. If I fix it and run it directly on my database it works fine. Am I doing something wrong here? Could this be a bug in Hibernate or in the Oracle driver?

I'm using EJB3 RC2 on JBoss 4.0.3RC2 and Oracle 10g.

1. Re: Subquery problem

epbernard Oct 7, 2005 3:02 PM (in response to dsouza)

children is a collection so can't equals a single element
Actions
2. Re: Subquery problem

dsouza Oct 7, 2005 3:17 PM (in response to dsouza)
Yes! Thank you! I should have seen that! There's even a much easier way to do this:

select child from ProductClass as child where size(child.parents) = 0

Denis
Actions

Go to original post