Extra Cross Join with Spring Data JPA & Querydsl

Question

I am using Querydsl 2.9, Spring Data JPA 1.3.0 and Hibernate JPA 2 API version 1.0. I'm attempting to do a simple join between two tables, Parent and Child, joining on a parentId column. For some reason the query that is executed by Hibernate always has an extra cross join in it. The tables look like this:

CREATE TABLE PARENT (
    PARENTID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(255)
);

CREATE TABLE CHILD (
    CHILDID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    PARENTID INT(11),
    NAME VARCHAR(255)
);

The domain classes look like this:

@Entity
@Table(name="PARENT")
public class Parent {

    @Id
    @GeneratedValue
    private Integer parentId;

    private String name;

    @OneToMany
    @JoinColumn(name="parentId")
    private List<Child> children;

    // ... getters/setters omitted for brevity
}

@Entity
@Table(name="CHILD")
public class Child {

    @Id
    @GeneratedValue
    private Integer childId;

    private Integer parentId;

    private String name;

    // ... getters/setters omitted for brevity
}

My query code looks like this:

private List<Parent> test(List<Integer> parentIds) {
    JPAQuery query = new JPAQuery(entityManagerFactory.createEntityManager());
    QParent qParent = QParent.parent;
    QChild qChild = QChild.child;

    List<Parent> parents = query.from(qParent, qChild)
        .innerJoin(qParent.children, qChild)
        .where(qParent.parentId.in(parentIds))
        .list(qParent);

        return parents;
}

I would expect the generated query to look something like this:

select 
    p.parentId, p.name 
from 
    parent p
        inner join 
    child c on c.parentid = p.parentid 
where 
    p.parentid in(1, 2);

However, the query that is actually run is this:

select 
    parent0_.parentId as parentId1_3_, parent0_.name as name2_3_
from
    PARENT parent0_
        inner join
    CHILD children2_ ON parent0_.parentId = children2_.parentId
        cross join
    CHILD child1_
where
    parent0_.parentId in (1 , 2);

Notice the extra cross join at the end. I realize I can get correct results if I do a group by on childId, but I don't want the extra overhead of the cross join when it's not necessary. I've tried using both innerJoin and join to no avail. I have scoured the Querydsl docs and I can see that the default join type is cross join, so perhaps I'm not specifying my join correctly? How do I get rid of the extra cross join?

Timo Westkämper Timo Westkämper · Accepted Answer · 2013-06-12T17:28:44

The second from argument is probably responsible for the cross join.

Try this instead

List<Parent> parents = query.from(qParent)
    .innerJoin(qParent.children, qChild)
    .where(qParent.parentId.in(parentIds))
    .list(qParent);

Extra Cross Join with Spring Data JPA & Querydsl

1 Answers