Home » Support » Forum » Query with FETCH JOIN returns multiple results instead of one.

Query with FETCH JOIN returns multiple results instead of one.

#1

Okay, I'll be honest: I thought I am familiar with JPA on top of ORM solutions, but now when moved to ObjectDB I sometimes encounter, let's say, unexpected behavior and I begin to doubt in my JPA-fu. Sometimes I am not really sure if behavior I encounter with ObjectDB is correct or not, although I am pretty sure it's not consistent with other JPA providers. And here is such case:
 

List jpqlResult = em.createQuery(
  "SELECT d FROM Demand d JOIN FETCH d.services WHERE d=:demand")
    .setParameter("demand", demand).getResultList();

I want this query to find one and only Demand object and fetch its services. Demand object is identified and found correctly, but as it is associated with two services, it's returned twice (both results contain the same instance). I believe that such query should return only one result, with fetched service sub-elements, shouldn't it? I am almost sure (but I did not bother to check, sorry!) that JPA+Hibernate would return only one result. Am I wrong? Is it something specific to ObjectDB? Is it a bug?

This behavior appears with JPQL and with equivalent CriteriaQuery.

 

Thanks and best regards,

   Maciek

#2

You should be able to filter duplicates with DISTINCT:

SELECT DISTINCT d FROM Demand d JOIN FETCH d.services WHERE d=:demand

You are probably wrong regarding Hibernate etc. as the JPA 2.1 specification on page 178 is clear:

SELECT d
FROM Department d LEFT JOIN FETCH d.employees
WHERE d.deptno = 1

"A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced in the query.  Hence, for example, if department 1 has five employees, the above query returns five references to the department 1 entity."

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#3

Wow, your answer was quick, thanks!

In the meantime I checked spec, googled, searched StackOverflow, and found out that JPA spec clearly mentions that duplicates are not filtered unless DISTINCT is used, but many JPA providers do this (or used to do) anyway. So it appears that ObjectDB is more conforming to spec than some of others :)

Sorry for bothering you, next time I do research first, and post later ;)

 

Thanks!


Post Reply

To post a reply and/or subscribe to update notifications - please login