Spring boot pagination with native query throws NumberFormatException

The error started to show up when I added pagination (using Pageable) to the search query with ORDER BY ?#{#pageable}. I'm using H2 database for unit-testing with database-platform: org.hibernate.dialect.H2Dialect.

Spring boot version: 1.5.9.RELEASE Java version: 1.8

Repository:

@Repository public interface VacancyRepository extends PagingAndSortingRepository<Vacancy, Long> { @Query(value = "SELECT * FROM vacancies WHERE location ILIKE %:location% AND CONCAT(title, ' ', description) ILIKE %:keyword% ORDER BY ?#{#pageable}", countQuery = "SELECT COUNT(*) FROM vacancies WHERE location ILIKE %:location% AND CONCAT(title, ' ', description) ILIKE %:keyword%", nativeQuery = true) Page<Vacancy> search(@Param("location") String location, @Param("keyword") String keyword, Pageable pageable); } 

Controller method (controller is prefixed with /vacancy/):

@RequestMapping(method = RequestMethod.GET, value = "/search/location/{location}/keyword/{keyword}", produces = MediaType.APPLICATION_JSON_VALUE) public ResponseEntity<Page<Vacancy>> search(@PathVariable String location, @PathVariable String keyword, Pageable pageable) { Page<Vacancy> vacancies = vacancyRepository.search(location, keyword, pageable); return ResponseEntity.ok().body(vacancies); } 

How it is used in tests:

 // Given String path = "/vacancy/search/location/searchQueryLocation/keyword/search?page=0&size=1&sort=id"; // When ResultActions sendRequest = mvc.perform(get(path)); 

Exception thrown:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:160) at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:127) at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:155) at uk.gov.cshr.vcm.controller.VacancyControllerTest.testSearch(VacancyControllerTest.java:336) 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.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:104) at org.testng.internal.MethodInvocationHelper$1.runTestMethod(MethodInvocationHelper.java:205) at org.springframework.test.context.testng.AbstractTestNGSpringContextTests.run(AbstractTestNGSpringContextTests.java:175) at org.testng.internal.MethodInvocationHelper.invokeHookable(MethodInvocationHelper.java:217) at org.testng.internal.Invoker.invokeMethod(Invoker.java:641) at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:851) at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1177) at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:129) at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:112) at org.testng.TestRunner.privateRun(TestRunner.java:756) at org.testng.TestRunner.run(TestRunner.java:610) at org.testng.SuiteRunner.runTest(SuiteRunner.java:387) at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:382) at org.testng.SuiteRunner.privateRun(SuiteRunner.java:340) at org.testng.SuiteRunner.run(SuiteRunner.java:289) at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52) at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86) at org.testng.TestNG.runSuitesSequentially(TestNG.java:1293) at org.testng.TestNG.runSuitesLocally(TestNG.java:1218) at org.testng.TestNG.runSuites(TestNG.java:1133) at org.testng.TestNG.run(TestNG.java:1104) at org.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:72) at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:123) Caused by: org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:282) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:503) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:209) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) at com.sun.proxy.$Proxy111.search(Unknown Source) at uk.gov.cshr.vcm.controller.VacancyController.search(VacancyController.java:82) 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.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ... 36 more Caused by: org.hibernate.exception.DataException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:118) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) at org.hibernate.loader.Loader.getResultSet(Loader.java:2117) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) at org.hibernate.loader.Loader.doQuery(Loader.java:919) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2617) at org.hibernate.loader.Loader.doList(Loader.java:2600) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) at org.hibernate.loader.Loader.list(Loader.java:2424) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:188) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:87) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:499) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:477) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ... 59 more Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "aced00057372002b6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e5061676552657175657374c0f950c5c09dc7260200014c0004736f72747400264c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f72743b787200336f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e41627374726163745061676552657175657374111be055001d2dc70200024900047061676549000473697a6578700000000000000001737200246f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f72744f9e94bc46c7fa210200014c00066f72646572737400104c6a6176612f7574696c2f4c6973743b7870737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a657870000000017704000000017372002a6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244f7264657215210bb2a35266bb0200045a000a69676e6f7265436173654c0009646972656374696f6e7400304c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f727424446972656374696f6e3b4c000c6e756c6c48616e646c696e677400334c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f7274244e756c6c48616e646c696e673b4c000870726f70657274797400124c6a6176612f6c616e672f537472696e673b7870007e72002e6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f727424446972656374696f6e00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400034153437e7200316f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244e756c6c48616e646c696e6700000000000000001200007871007e000f7400064e4154495645740002696478"; SQL statement: SELECT * FROM vacancies WHERE location ILIKE ? AND CONCAT(title, ' ', description) ILIKE ? ORDER BY ?, id asc limit ? [22018-196] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) at org.h2.message.DbException.get(DbException.java:168) at org.h2.value.Value.convertTo(Value.java:996) at org.h2.value.Value.getInt(Value.java:459) at org.h2.command.dml.Query.prepareOrder(Query.java:518) at org.h2.command.dml.Select.prepare(Select.java:834) at org.h2.command.CommandContainer.recompileIfRequired(CommandContainer.java:88) at org.h2.command.CommandContainer.query(CommandContainer.java:109) at org.h2.command.Command.executeQuery(Command.java:201) at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:111) at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy124.executeQuery(Unknown Source) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 88 more Caused by: java.lang.NumberFormatException: For input string: "aced00057372002b6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e5061676552657175657374c0f950c5c09dc7260200014c0004736f72747400264c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f72743b787200336f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e41627374726163745061676552657175657374111be055001d2dc70200024900047061676549000473697a6578700000000000000001737200246f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f72744f9e94bc46c7fa210200014c00066f72646572737400104c6a6176612f7574696c2f4c6973743b7870737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a657870000000017704000000017372002a6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244f7264657215210bb2a35266bb0200045a000a69676e6f7265436173654c0009646972656374696f6e7400304c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f727424446972656374696f6e3b4c000c6e756c6c48616e646c696e677400334c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f7274244e756c6c48616e646c696e673b4c000870726f70657274797400124c6a6176612f6c616e672f537472696e673b7870007e72002e6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f727424446972656374696f6e00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400034153437e7200316f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244e756c6c48616e646c696e6700000000000000001200007871007e000f7400064e4154495645740002696478" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) at java.lang.Long.parseLong(Long.java:592) at org.h2.value.Value.convertTo(Value.java:641) 

Judging by the "aced" in the beginnig, it looks like a serialized Java object, but I don't see where it is serialized.

Generated Hibernate log:

Hibernate: SELECT * FROM vacancies WHERE location ILIKE ? AND CONCAT(title, ' ', description) ILIKE ? ORDER BY ?, id asc limit ? 2018-01-04 15:08:02.940 WARN 6872 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 22018, SQLState: 22018 2018-01-04 15:08:02.940 ERROR 6872 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Data conversion error converting "aced00057372002b6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e5061676552657175657374c0f950c5c09dc7260200014c0004736f72747400264c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f72743b787200336f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e41627374726163745061676552657175657374111be055001d2dc70200024900047061676549000473697a6578700000000000000001737200246f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f72744f9e94bc46c7fa210200014c00066f72646572737400104c6a6176612f7574696c2f4c6973743b7870737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a657870000000017704000000017372002a6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244f7264657215210bb2a35266bb0200045a000a69676e6f7265436173654c0009646972656374696f6e7400304c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f727424446972656374696f6e3b4c000c6e756c6c48616e646c696e677400334c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f7274244e756c6c48616e646c696e673b4c000870726f70657274797400124c6a6176612f6c616e672f537472696e673b7870007e72002e6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f727424446972656374696f6e00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400034153437e7200316f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244e756c6c48616e646c696e6700000000000000001200007871007e000f7400064e4154495645740002696478"; SQL statement: SELECT * FROM vacancies WHERE location ILIKE ? AND CONCAT(title, ' ', description) ILIKE ? ORDER BY ?, id asc limit ? [22018-196] 

The same query without Pageable works as expected. I tried different ways of writing the same Pageable thing (including ORDER BY /*#pageable*/ or ORDER BY t.id DESC \n-- #pageable\n or by trying to comment the #pageable in the query (/*#pageable*/) and passing my own page and page size parameters) but nothing works.

I know that I can replace this particular query with somethig that uses to_upper() instead of ILIKE and use JPA queries, but the following steps will imply something that is not implemented in JPA queries and requires native queries.

Any help would be much appreciated.