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

    Subquery problem

    Denis Souza Newbie

      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.