querydsl-jpa: orderBy path generates a cross join when it should generate a left join

[thomasdarimont]

One of our spring-data-jpa users noticed that a join is generated when he tried to order a querydsl query by a nested property path.

Since we generate the full querydsl query dynamically based on the given predicates I created a plain querydsl version for easier confirmability (and to make sure the problem is not in spring-data-jpa):

    @Override
    public Page<Mail> findByContent2(final String content, final Pageable page) {
        final QMail mail = QMail.mail;
        return new PageImpl<Mail>(new JPAQuery(entityManager).from(mail)            .where(mail.content.eq(content)).orderBy(mail.sender.name.asc()).list(mail));
    }

This is the translated JPA/SQL query for hibernate:

   /* select
        mail 
    from
        Mail mail 
    where
        mail.content = ?1 
    order by
        mail.sender.name asc */ select
            mail0_.id as id0_,
            mail0_.content as content0_,
            mail0_.sender_id as sender3_0_ 
        from
            Mail mail0_ cross 
        join
            Sender sender1_ 
        where
            mail0_.sender_id=sender1_.id 
            and mail0_.content=? 
        order by
            sender1_.name asc

I think the bug is, that it generates a cross join instead of a left join in this case.

I also tried to add the left join dynamically:

@Override
    public Page<Mail> findByContent2(final String content, final Pageable page) {
        final QMail mail = QMail.mail;
        return new PageImpl<Mail>(new JPAQuery(entityManager).from(mail).leftJoin(mail.sender)
                .where(mail.content.eq(content)).orderBy(mail.sender.name.asc()).list(mail));
    }

This produced:

    /* select
        mail 
    from
        Mail mail   
    left join
        mail.sender 
    where
        mail.content = ?1 
    order by
        mail.sender.name asc */ select
            mail0_.id as id0_,
            mail0_.content as content0_,
            mail0_.sender_id as sender3_0_ 
        from
            Mail mail0_ 
        left outer join
            Sender sender1_ 
                on mail0_.sender_id=sender1_.id cross 
        join
            Sender sender2_ 
        where
            mail0_.sender_id=sender2_.id 
            and mail0_.content=? 
        order by
            sender2_.name asc

As we can see here, there is still a cross join generated and the order by clause uses the crossjoin alias instead of the alias from the left outer join.

For further information (Test-case and Domain classes) see (the attachment in):
https://jira.springsource.org/browse/DATAJPA-427

-> net.croz.repository.querydsl.extended.MailDslRepositoryExtendedImpl#findByContent

[timowest]

The join methods with one argument are mostly useful for join(prop).fetch() cases, for your case the following should work

     final QMail mail = QMail.mail;
     final QSender sender = QSender.sender;
     return new PageImpl<Mail>(new JPAQuery(entityManager).from(mail).leftJoin(mail.sender, sender)
                .where(mail.content.eq(content)).orderBy(sender.name.asc()).list(mail));

Reuse of implicit joins is not guaranteed, so if you refer to a property path with references in one part of the query you can't be sure that the related join is reused elsewhere.

Querydsl JPA serializes the query quite directly to JPQL.

Reuse Implicit joins are not reused as far as I know, so when you

[thomasdarimont]

Okay, thanks for the quick response :) It you suggestion works :) 👍

[timowest]

Could you try again with the orderBy part of my example?

[thomasdarimont]

As said it works now - didn't realize the changed orderBy part... thx again 👍
I try to generalize that for SD JPA.

But I think would be helpful if you documentation would mention this.