When using a native SQL query and applying a Pageable with sorting, the generated SQL can become malformed with missing order by clause when inner order by clauses exist in query

[jon-nielsen]

Description:
When using a native SQL query and applying a Pageable with sorting, the generated SQL can become malformed with missing order by clause when inner order by clauses exist in query

To reproduce:

1. Create a native query string with some inner selects with order clauses
2. End inner select like " order by something desc)"
3. Run query and get error like "ORA-00933: SQL command not properly ended..." in Oracle
4. Notice logged SQL end with: "where (...), something desc fetch first ? rows only"

Details:
I had a look at JPA repository query package to look for hints for problems with my SQL. Found this:

https://github.com/spring-projects/spring-data-jpa/blame/dbd4e532d2eb229b8449458f7851e9ec6e814cfc/spring-data-jpa/src/main/java/org/springframework/data/jpa/repository/query/QueryUtils.java#L301

In QueryUtils

• applySorting is used to either append ", " or " order by " to a query.
• hasOrderByClause is used to determine if top-level "order by" exists: countOccurrences(ORDER_BY, query) > countOccurrences(ORDER_BY_IN_WINDOW_OR_SUBSELECT, query).
• ORDER_BY_IN_WINDOW_OR_SUBSELECT only matches order by within ( ).

Workaround:

1. Added "order by null" to native query
2. Run query
3. Notice logged SQL end with: "where (...) order by null, something desc fetch first ? rows only"

[mp911de]

Thanks for reaching out. Our own support is limited to simple queries only as we do not maintain a fully SQL compliant parser, instead we apply limited regex replacements. If you want to use more complex queries, please add JSqlParser (see reference docs) or provide the count query yourself.

[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.