Regression in 1.5.1 - Sort by property of an associated object doesn't work. Join property and INNER vs OUTER join when sorting [DATAJPA-510]

[spring-projects-issues]

Matt Byrne opened DATAJPA-510 and commented

Order by a column on a join table seems to be filtering out null values again (INNER JOIN vs OUTER JOIN?). This is with an Oracle-backed db.

DATAJPA-346 fixed this issue, however after upgrading to 1.5.1 it has come back.

Library versions are: spring-data-jpa 1.5.1.RELEASE querydsl 3.3.2 hibernate 4.3.4.Final

When I try to sort on a joined column, the null joins are filtered out. When I change spring-dta-jpa to 1.5.0.RELEASE it works again without filtering with all other dependencies the same version.

Seems like a regression in 1.5.1.

The offending generated query is shown in both versions below

select
    * 
from
    (
    select
        <<all-my-select-cols>>
    from
        REQUEST request0_ 
    left outer join
        SITE_USER siteuser1_ 
            on request0_.ASSIGNED_TO=siteuser1_.ID 
    where
        request0_.ROW_END_DATE is null 
    order by
        lower(siteuser1_.FIRST_NAME) asc,
        lower(siteuser1_.LAST_NAME) asc) 
where
    rownum <= ?

select
    * 
from
    (
    select
        <<all-my-select-cols>>
    from
        REQUEST request0_,
        SITE_USER siteuser1_ 
    where
        request0_.ASSIGNED_TO=siteuser1_.ID 
        and (
            request0_.ROW_END_DATE is null
        ) 
    order by
        lower(siteuser1_.FIRST_NAME) asc) 
where
    rownum <= ?

It also looks like my secondary order by column is missing (LAST_NAME), as the only thing I changed between runs was the spring-data-jpa version.

---

Affects: 1.5.1 (Codd SR1)

Reference URL: https://jira.spring.io/browse/DATAJPA-346

Attachments:

• test-case.zip (9.31 kB)

Referenced from: pull request https://github.com/spring-projects/spring-data-jpa/pull/87

Backported to: 1.5.3 (Codd SR3)

[spring-projects-issues]

Thomas Darimont commented

Hi Matt,

Thanks reporting this - would you mind giving the latest snapshots (or 1.6M1 which comes out today) a try? Also it would help a bit if you could provide a small test case that reproduces the issue.

Cheers, Thomas

[spring-projects-issues]

Matt Byrne commented

Hi Thomas - I've tested this against 1.6M1 and still get the same results. I'm trying to adapt the test-case written in DATAJPA-277 to prove my failure but having difficulties so will get back to you.

Just trying to simplify the code that comes from an inherited Roo codebase to prove the bug

[spring-projects-issues]

Matt Byrne commented

I've finally been able to reproduce this in a test case - was driving me crazy trying to get it to fail. I've used the test-case.zip from DATAJPA-277 as a base, updated all plugins, libraries, etc and got it working in jdk 1.7.

I've added two tests to the same test class to specifically test using QueryDsl and the one that fails is when you specify a sort order with ignoreCase().

The version in the pom.xml is 1.5.1.RELEASE but when you change it to 1.5.0.RELEASE it passes. It will also fail with 1.6.0.M1.

[spring-projects-issues]

Thomas Darimont commented

Hello,

in the 1.5.0 we did the join generation ourselves since querydsl didn't do it on it's own. Since the latest querydsl version 3.3.2 provided support for it we removed our custom join logic.

The problem in this case seems to be that querydsl doesn't generate the appropriate joins if the property to order by is ordered by a function (e.g. lower()).

I created https://github.com/mysema/querydsl/issues/698 to discuss this with the querydsl devs.

Cheers, Thomas

[spring-projects-issues]

Thomas Darimont commented

This problem is now fixed in the querydsl master branch. We now wait for a new version of querydsl to come out

[spring-projects-issues]

Thomas Darimont commented

This is solved now by the new release of querydsl 3.3.3 to which we just upgraded the spring-data-build to.

Unfortunately we didn't mange to get this into the RC1 release. Until the next release is out we recommend to update your querydsl dependency to 3.3.3 as a workaround.

[spring-projects-issues]

Thomas Darimont commented

Please review