Order by wrongly added to a native query

[michal-trnka]

Hello,
I have found an issue in 2.7.1 with adding order by by sort to a query that already has order by.
It happens only in specific scenarios/combinations of the clauses. See the test method bellow.

@Test
void testOrderBy(){
    Sort sort = Sort.by(Sort.Order.desc("age"));

    //works
    assertThat(QueryUtils.applySorting("SELECT * FROM t \n" //
            + "WHERE true \n"
            + "ORDER BY (case when t.id in :priorityIds then 1 else 2 end)\n" //
            + "", sort))
            .endsWith("ORDER BY (case when t.id in :priorityIds then 1 else 2 end)\n" + ", age desc");

    //works
    assertThat(QueryUtils.applySorting("SELECT * FROM t \n" //
            + "WHERE (true OR false) \n"
            + "ORDER BY t.id\n" //
            + "", sort))
            .endsWith("ORDER BY t.id\n" + ", age desc");

    //not working
    assertThat(QueryUtils.applySorting("SELECT * FROM t \n" //
            + "WHERE (true OR false) \n"
            + "ORDER BY (case when t.id in :priorityIds then 1 else 2 end)\n" //
            + "", sort))
            .endsWith("ORDER BY (case when t.id in :priorityIds then 1 else 2 end)\n" + ", age desc");

}

It seems to be related to this change of the regex pattern: 6fd829c .

The issue is not present in 2.7.0.

[grzegorz-aniol]

the valid workaround is to remove parenthesis after ORDER BY
ORDER BY (case when t.id in :priorityIds then 1 else 2 end) => ORDER BY case when t.id in :priorityIds then 1 else 2 end

[lith-imad]

another case where a workaround is not as trivial as when using native database functions:

select e from SampleEntity e where function('nativeFunc', ?1) > 'testVal' order by function('nativeFunc', ?1);

spring data will add extra order by as it incorrectly resolves subsequent function presence as subquery or window query.

[gregturn]

We are working on a parsing solution to better handle such situations that QueryUtils accommodates. For the meantime, we are trying to avoid any more changes to QueryParser.

[gregturn]

@lith-imad Your query appears to be Hibernate while the original reporter appears to be commenting on a SQL query. I have opened #2862 to separately track your issue.

[gregturn]

Since this involves native queries and not the new parsers (HQL/JPQL), I'm dropping the revisit-after-parser-rewrite label.

[gregturn]

I'm removing the query-parser tag since this issue involves native queries, which we currently have limited ability to deal with.

Frankly, this situation appears too complicated for QueryUtils to handle, and we aren't likely to approve a change there that would meet your needs. Your best best moving forward may be to implement a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository.

[spring-projects-issues]

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

[spring-projects-issues]

Closing due to lack of requested feedback. If you would like us to look at this issue, please provide the requested information and we will re-open the issue.