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.