Native query with #sort generates a broken query

[paulbakker]

Although the docs mention that dynamic sorting is not supported with native queries, the following almost works:

 @Query(
            nativeQuery = true,
            value = "select * from show #sort"
    )
    List<Show> showsNativeWithoutWorkaround(Sort sort);

It generates the following query when sorting by a title field: select * from show #sort ORDER BY title asc.
Note that it added the correct ORDER BY, but didn't remove the #sort placeholder.

I've created a simple example to reproduce here: https://github.com/paulbakker/spring-data-jpa-sort-bug.
See the README for details about the code.

The example uses a Postgres Testcontainer, so no extra setup is needed.

A hacky workaround is to add a custom query rewrite like this:

public class SortFixQueryRewriter implements QueryRewriter {
    @Override
    public String rewrite(String query, Sort sort) {
        return query.replace("#sort", "");
    }
}

[mp911de]

Thanks for the report. This is a leftover from the earlier restrictions on native query sorting. Meanwhile, we've added support for query enhancing through JSQLParser that should lift the restriction for many cases where the regex-based enhancer isn't capable of adding ORDER BY.

We should remove this requirement to avoid the need for custom string replacement.

[paulbakker]

Thanks @mp911de !