NativeQuery with Pagination

[Johnczek]

Hello, I am facing issue similiar with #1282.

I am trying to run this native query with spring-data on version 2.5.0

@Query(value = "SELECT dense_rank() OVER (ORDER BY rank DESC)," + "t.id, " + "FROM table t WHERE t.x > 0 ", nativeQuery = true) Page<Object[]> getT(Pageable pageable);

But I am getting SQLGrammarException because the spring-data engine will translate this query into

SELECT dense_rank() OVER (ORDER BY rank DESC),t.id FROM table t WHERE t.x > 0 , t.y desc limit 20

Where the problem is pretty obvious. There is ORDER BY clause missing, probably because of presence of ORDER BY clause in OVER subquery. Is this a known bug? And is it possible to bypass this somehow? I need to use whole pageable object with native query with this dense_rank() calling.

[schauder]

I suspect this is a bug in the underlying JPA implementation. Spring Data JPA uses Query.setFirstResult and Query.setMaxResult to create the queries for pagination.

Could you confirm that you seeing this behaviour as well if you use an EntityManager directly, as described in https://www.baeldung.com/jpa-pagination

[Johnczek]

I suspect this is a bug in the underlying JPA implementation. Spring Data JPA uses Query.setFirstResult and Query.setMaxResult to create the queries for pagination.

Could you confirm that you seeing this behaviour as well if you use an EntityManager directly, as described in https://www.baeldung.com/jpa-pagination

Hello, I´ve tried many ways but entity manager did not worked for me cause native query has no option to pass sort into query.

But after some investigation I´ve found a workaround with adding ORDER 1 at the end of query and now It is working fine.
I assume that spring will look for ORDER BY substring and if its found, there is (from his point of view) no need to add another ORDER BY and therefore spring appends just , ordering limiting offsetting. But in this case the ORDER BY clause should be added because the found ORDER BY property is not the one that framework is looking for.

[TAndronicus]

I'll try to tackle this issue

[TAndronicus]

I have added a solution where I'm counting the number of occurrences of order by and order by used in window functions. If the former count is greater than the latter, I'm assuming the order by is already present.
Although this fixes the reported issue, I can think of an example where it won't work - when order by is used in nested query (as in @shu1997 comment). Any suggestions are welcome.
Maybe it would be better to exclude all order by matches surrounded by parentheses - this would solve all the issues provided the entire query is not surrounded with parentheses.

[TAndronicus]

I've changed the pattern to match subqueries.
At the same time there was a bug in alias detection - when using subqueries, it would take the first alias - used in subquery.

[somayaj]

Hi, can this be worked?

[gregturn]

Thanks @TAndronicus!

[Persi]

Hi @TAndronicus, you mentioned a bug concerning alias detection. After upgrading from Spring Boot 2.5.12 to 2.5.13 (which implicitely upgrades spring-data-jpa from 2.5.10 to 2.5.11), some of our JPA queries did not work anymore because of false aliases used in queries. The problem only seem to occur in functions using @Query annotated repository methods, which use generic Sort or Paging mechanics of Spring Data JPA.

Example showing code which works with 2.5.10 of spring-data-jpa, but fails with 2.5.11:

@Query("SELECT r FROM Rolle r where " +
	"(lower(r.name) like concat('%', lower(:name), '%')) AND " +
	"(:deleted is null or r.deleted = :deleted) AND " +
	"(:systemrolle is null or r.systemRolle = :systemrolle) AND " +
	"(COALESCE(:rechtIds, null) is null or (" +
	"select count(r2) from Rolle r2 join r2.rechtIds rIds " +
	"where rIds in (:rechtIds) " +
	"AND r.id = r2.id " +
	"group by r2.id having count(r2.id) = :minNumberOfMatches ) > 0)")
Page<Rolle> findAllByNameContainsIgnoreCaseAndDeletedAndSystemrolleAndRechtIdsIn(
	@Param("name") @NotNull String name,
	@Param("deleted") Boolean deleted,
	@Param("systemrolle") Boolean systemrolle,
	@Param("rechtIds") Set<RechtId> rechtIds,
	@NotNull Long minNumberOfMatches, Pageable pageable);

The occurring error is as follows:
... Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'r2.name' [SELECT r FROM rechte_rollen.model.Rolle r where (lower(r.name) like concat('%', lower(:name), '%')) AND (:deleted is null or r.deleted = :deleted) AND (:systemrolle is null or r.systemRolle = :systemrolle) AND (COALESCE(:rechtIds, null) is null or (select count(r2) from rechte_rollen.model.Rolle r2 join r2.rechtIds rIds where rIds in (:rechtIds) AND r.id = r2.id group by r2.id having count(r2.id) = :minNumberOfMatches ) > 0) order by r2.name desc] ...

As you can see in the error, the generated sql is finalized by "order by r2.name desc", but r2 is the alias used in the subquery, not the alias used for the whole query.

Can you please take a look? I am unsure if we do something wrong, or if this is a new bug introduced with 2.5.11.

With best regards,
Marcus

[edudar]

I can confirm, the change in alias detection broke quite a few things:

while (matcher.find()) {
  alias = matcher.group(2);
}

In my case, I have a field that ends with From, alias detection uses from as a starting point, and the query alias moves.

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e 
WHERE (CAST(:modifiedTo AS date) IS NULL OR e.modificationDate >= :modifiedTo)"
); -> "e"

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e 
WHERE (CAST(:modifiedFrom AS date) IS NULL OR e.modificationDate >= :modifiedFrom)"
); -> "date"

[TAndronicus]

Thank you @Persi and @edudar for reporting, I will probably have time to take a look next week. Thank you for minimal example @edudar

[slPerryRhodan]

I can confirm, the change in alias detection broke quite a few things:

while (matcher.find()) {
  alias = matcher.group(2);
}

In my case, I have a field that ends with From, alias detection uses from as a starting point, and the query alias moves.

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e 
WHERE (CAST(:modifiedTo AS date) IS NULL OR e.modificationDate >= :modifiedTo)"
); -> "e"

QueryUtils.detectAlias(
"SELECT e FROM DbEvent e 
WHERE (CAST(:modifiedFrom AS date) IS NULL OR e.modificationDate >= :modifiedFrom)"
); -> "date"

I can confirm this behavior. We have the same error.

[edudar]

@gregturn does not look like #2516 fixes this case: #2260 (comment) as there are no sub-queries yet alias detection is affected by the change in this ticket.

[darinmanica]

@edudar this is due to the lack of a zero-width word boundary in a regex that predates #2216; however, it is revealed by the PR. I've added the word boundary and unit test in a new PR, #2521.

[gregturn]

Fixed on main.

Backported to 2.7.x, 2.6.x, and 2.5.x.

Thanks @darinmanica!

[gregturn]

Thanks to everyone else that helped tackle this issue with feedback and links.

[ronghuiye-agi]

hi @gregturn, does this issue get fixed? I am using 2.7.1 but still seeing the sorting with wrong table

[PAX523]

I watched the same issue with 2.7.1, even if it should already be fixed

[schauder]

@PAX523 please create a new issue and include a reproducer.

[darinmanica]

@PAX523 @ronghuiye-agi There is an issue in #2563 where the alias is incorrectly detected in multi-line queries. I just submitted #2603 to address this. Does your query contain EOL characters, such as a java text block?

[PAX523]

Yes, that's the case! I cannot inspect the used EOL characters right now, but basically the query consists of Java's multi-line string feature:

 """
  SELECT * FROM ...
   WHERE ...
  """

Thank you for working on it. Cheers PAX