NativeQuery with Pagination validation error at startup [DATAJPA-928]

[spring-projects-issues]

Miguel Martín-Forero opened DATAJPA-928 and commented

According to Example 50 at Using @Query docs, it's possible to use a native query with pagination using Pageable but in my case it's failing with a org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException.

NativeJpaQuery constructor is checking if the query has a Pageable parameter and if the queryString contains a #pageable or #sort sequence. The query has Pageable parameter but it does not contain a #pageable string:

@Query(value = "select m.* from message m left join user_prefers_category u on (u.category = m.category and u.user = ?1) join message_category c on c.id = m.category where u.categoryEnabled = 1 or m.category not in (select category from user_prefers_category us where us.user = ?1)",
            countQuery = "select count(m.*) from message m left join user_prefers_category u on (u.category = m.category and u.user = ?1) join message_category c on c.id = m.category where u.categoryEnabled = 1 or m.category not in (select category from user_prefers_category us where us.user = ?1)",
    nativeQuery = true)
    Page<Message> findByUserCategories(Integer userId, Pageable pageable);

If I provide a #pageable string at the end of the query, validation passes, but when the query executes, it fails saying that it's expecting 3 parameters instead of 2.

Funny thing is that, when the server is starting, if I set a breakpoint inside NativeJpaQuery and change containsPageableOrSortInQueryExpression from false to true manually, validation passes just fine and the query executes well, paginating

---

Affects: 1.10.1 (Hopper SR1), 1.10.2 (Hopper SR2)

Reference URL: http://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination

Issue Links:

• DATAJPA-1255 Native query with SpEL creates invalid SQL

Referenced from: pull request #246, and commits 64c668d, b7ca812, 68efc51, 8bf8e3a

Backported to: 2.0.4 (Kay SR4)

1 votes, 12 watchers

[spring-projects-issues]

Dmitry Stolbov commented

Try add this ORDER BY code to end of string value:

value = "SELECT ...  ORDER BY ?#{#pageable}",

Is working in my case with PostgreSQL

[spring-projects-issues]

wei mao commented

I also encountered this bug months ago.
Thanks Dmitry. This hack works with MySql also.
I hope this could get fixed soon

[spring-projects-issues]

ssd1980 commented

I faced same issue in Oracle. Adding hack ?#{#pageable} did not resolve my problem.
After doing some analysis found that in case of oracle we need to use slightly different hack. Adding /*#pageable*/' to query string resolved my problem.

[spring-projects-issues]

Nikola Maric commented

Adding those workarounds for H2 database doesn't work.
Affected versions: spring-data-jpa 1.10.5, h2 1.4.193.

EDIT: This works:

"ORDER BY o.id DESC \n-- #pageable\n",

[spring-projects-issues]

Jens Schauder commented

Turns out Pageable works for native Query just fine if one removes the check that prevents it.
It's implemented (possibly accidental) via EntityManager.createNativeQuery(sql).setMaxResults/setFirstResult which then causes the JPA Implementation to modify the query so it only returns the desired rows, just as it would do it for a JPQL query.

The only effect of the various workarounds with #pageable is to circumvent the check that prevents a native query with a Pageable parameter.
An upcoming PR will remove the check

[spring-projects-issues]

Rüdiger Schulz commented

After updating to spring-boot 2.0.1 from 1.5, my PageAble native queries just ignore the Pageable parameter and query without limit. Which is a big problem, obviously.

Before I applied the workaround as posted here:
https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination

Other methods in the same repository using simple naming convention use the pageable.

My method signature is e.g.

Page<User> searchByUsername(String username, Pageable pageable);

And I'm using MySQL.

Anything else that needs to be done?

[spring-projects-issues]

Rüdiger Schulz commented

I can answer that myself: remove the \n#pageable\n workaround from your native queries when upgrading

[spring-projects-issues]

Hendi Santika commented

I have that issue on Oracle @ssd1980.

I added on my query "/#pageable/".

But, where are the page, size, sort params have to passed?

Any idea?

Thanks

[spring-projects-issues]

Hendi Santika commented

I mean where is the limit ?

I am using Oracle Database.

Thanks

[spring-projects-issues]

Jens Schauder commented

hendisantika do not add /#pageable/ or similar to your query if you are using a version containing the fix for this issue. The appropriate limit clause will be added by the JPA implementation

[spring-projects-issues]

Hendi Santika commented

I already resolved that by adding/#pageable/ and limit.

Thanks

[spring-projects-issues]

Michel Jung commented

I had this \n#pageable\n workaround in place and it worked. Now, after upgrading to spring boot 2.0.4 (spring-data-jpa 2.0.9), the created query has no limit anymore.

 

This is my repository method

@Query(value = "SELECT" +
  "    ladder1v1_rating.id," +
  "    login.login," +
  "    ladder1v1_rating.mean," +
  "    ladder1v1_rating.deviation," +
  "    ladder1v1_rating.numGames," +
  "    ladder1v1_rating.winGames," +
  "    @s \\:= @s + 1 rank" +
  "  FROM ladder1v1_rating JOIN login on login.id = ladder1v1_rating.id," +
  "    (SELECT @s \\:= ?#{#pageable.offset}) AS s" +
  "  WHERE is_active = 1 AND ladder1v1_rating.numGames > 0" +
  "   AND login.id NOT IN (" +
  "     SELECT player_id FROM ban" +
  "     LEFT JOIN ban_revoke on ban.id = ban_revoke.ban_id" +
  "     WHERE (expires_at is null or expires_at > NOW()) AND ban_revoke.ban_id IS NULL" +
  "  ) " +
  "  ORDER BY round(mean - 3 * deviation) DESC",
  countQuery = "SELECT count(*) FROM ladder1v1_rating WHERE is_active = 1 AND ladder1v1_rating.numGames > 0",
  nativeQuery = true)
Page<Ladder1v1LeaderboardEntry> getLeaderboardByPage(Pageable pageable);

Which results in the query:

SELECT
  ladder1v1_rating.id,
  login.login,
  ladder1v1_rating.mean,
  ladder1v1_rating.deviation,
  ladder1v1_rating.numGames,
  ladder1v1_rating.winGames,
  @s := @s + 1 rank
FROM ladder1v1_rating
  JOIN login on login.id = ladder1v1_rating.id
  , (SELECT @s := ?) AS s
WHERE is_active = 1 AND ladder1v1_rating.numGames > 0 AND login.id NOT IN (SELECT player_id
                                                                           FROM ban
                                                                             LEFT JOIN ban_revoke
                                                                               on ban.id = ban_revoke.ban_id
                                                                           WHERE
                                                                             (expires_at is null or expires_at > NOW())
                                                                             AND ban_revoke.ban_id IS NULL)
ORDER BY round(mean - 3 * deviation) DESC 

And the exception:

2018-08-05 22:22:25.503  INFO 20128 --- [     Executor-1] j.r.q.QueryParameterSetter$ErrorHandling : Silently ignoring2018-08-05 22:22:25.503  INFO 20128 --- [     Executor-1] j.r.q.QueryParameterSetter$ErrorHandling : Silently ignoring
java.lang.IllegalArgumentException: Unknown parameter position: 1 at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:240) at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:503) at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:592) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:380) at com.sun.proxy.$Proxy307.setParameter(Unknown Source) at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$5(QueryParameterSetter.java:124) at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$2.execute(QueryParameterSetter.java:185) at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:124) at org.springframework.data.jpa.repository.query.ParameterBinder.lambda$bind$0(ParameterBinder.java:79) at java.util.ArrayList.forEach(ArrayList.java:1257) at java.util.Collections$UnmodifiableCollection.forEach(Collections.java:1080) at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:79) at org.springframework.data.jpa.repository.query.AbstractStringBasedJpaQuery.doCreateCountQuery(AbstractStringBasedJpaQuery.java:120) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createCountQuery(AbstractJpaQuery.java:237) at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.count(JpaQueryExecution.java:201) at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.lambda$doExecute$0(JpaQueryExecution.java:195) at org.springframework.data.repository.support.PageableExecutionUtils.getPage(PageableExecutionUtils.java:62) at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:194) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:136) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy244.getLeaderboardByPage(Unknown Source) 

 

Am I doing something wrong or does the fix not fix this specific case?

[spring-projects-issues]

Michel Jung commented

Alright, so when using

?#{#pageable.offset} 

within the query, hibernate does no longer append the LIMIT clause, so I added

LIMIT ?#{#pageable.offset},?#{#pageable.pageSize} 

to fix the problem

[spring-projects-issues]

Hendi Santika commented

Previously, When I was using Spring Boot 1.5.14 param /#pageable/ was working fine. But after I upgraded into Spring Boot 2.0.4 I've got this error : 

org.springframework.dao.InvalidDataAccessResourceUsageException: Named parameter [1] not set; nested exception is org.hibernate.QueryException: Named parameter [1] not set

 

If give param like this in pageable swagger page 0 size 20, It was working.

But, If I give param like this page 0 size 19, I've got above issue.

 

Any suggestion?

 

Thank You

[spring-projects-issues]

Jens Schauder commented

If you just want standard pagination you should remove the /#pageable or similar workarounds.

If you want to put the necessary constraints for pagination in your query yourself you need to properly reference the attributes of the pageable as Michael Jung describes in his comment.

If this doesn't solve your issue, please create a new issue. Please make sure to include the query you are using, preferable attach or link to a complete repository, reproducing the issue