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.