micronaut-projects / micronaut-projects/micronaut-data

Pageable JDBC query with extra parameters not used in countQuery fails

Open
#1,773 0 comments 0 reactions 1 assignee View on GitHub

@radovanradic is already working on this.

Since Nov 22, 2022.

type: bug
Dominant language
Java
Stars
482
Forks
229
Avg merge
1d 7h
Merged PRs (30d)
32

Description

Expected Behavior

When adding a pageable query to a repository interface that has a different number of parameters in the query to the countQuery, both queries should execute as expected returning the correct page of data.

Actual Behaviour

The repository method throws an error on the count query:

22:13:44.037 [default-nioEventLoopGroup-1-11] ERROR i.m.http.server.RouteExecutor - Unexpected error occurred: Unable to set PreparedStatement value: Parameter index out of range (2 > number of parameters, which is 1).
io.micronaut.data.exceptions.DataAccessException: Unable to set PreparedStatement value: Parameter index out of range (2 > number of parameters, which is 1).
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.newDataAccessException(JdbcQueryStatement.java:320)
	Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Assembly trace from producer [reactor.core.publisher.FluxLift] :
	reactor.core.publisher.Flux.error
	io.micronaut.http.server.RouteExecutor.lambda$executeRoute$14(RouteExecutor.java:666)
Error has been observed at the following site(s):
	*____________Flux.error ⇢ at io.micronaut.http.server.RouteExecutor.lambda$executeRoute$14(RouteExecutor.java:666)
	*__Flux.deferContextual ⇢ at io.micronaut.http.server.RouteExecutor.executeRoute(RouteExecutor.java:643)
	*__________Flux.flatMap ⇢ at io.micronaut.http.server.RouteExecutor.buildRouteResponsePublisher(RouteExecutor.java:614)
	|_         Flux.flatMap ⇢ at io.micronaut.http.server.RouteExecutor.buildResultEmitter(RouteExecutor.java:630)
Original Stack Trace:
		at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.newDataAccessException(JdbcQueryStatement.java:320)
		at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setString(JdbcQueryStatement.java:209)
		at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setString(JdbcQueryStatement.java:36)
		at io.micronaut.data.runtime.mapper.QueryStatement.setDynamic(QueryStatement.java:75)
		at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setDynamic(JdbcQueryStatement.java:123)
		at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setDynamic(JdbcQueryStatement.java:36)
		at io.micronaut.data.runtime.operations.internal.sql.AbstractSqlRepositoryOperations.setStatementParameter(AbstractSqlRepositoryOperations.java:235)
		at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.access$1700(DefaultJdbcRepositoryOperations.java:130)
		at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations$JdbcParameterBinder.bind(DefaultJdbcRepositoryOperations.java:939)
		at io.micronaut.data.runtime.operations.internal.sql.DefaultSqlStoredQuery.bindParameter(DefaultSqlStoredQuery.java:216)
		at io.micronaut.data.runtime.operations.internal.sql.DefaultSqlStoredQuery.bindParameters(DefaultSqlStoredQuery.java:137)
		at io.micronaut.data.runtime.operations.internal.sql.DefaultSqlPreparedQuery.bindParameters(DefaultSqlPreparedQuery.java:141)
		at io.micronaut.data.runtime.operations.internal.sql.SqlPreparedQuery.bindParameters(SqlPreparedQuery.java:74)
		at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.lambda$findOne$2(DefaultJdbcRepositoryOperations.java:290)
		at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.lambda$executeRead$18(DefaultJdbcRepositoryOperations.java:687)
		at io.micronaut.transaction.support.AbstractSynchronousStateTransactionManager.execute(AbstractSynchronousStateTransactionManager.java:145)
		at io.micronaut.transaction.support.AbstractSynchronousStateTransactionManager.executeRead(AbstractSynchronousStateTransactionManager.java:160)
		at io.micronaut.transaction.support.AbstractSynchronousTransactionManager.executeRead(AbstractSynchronousTransactionManager.java:150)
		at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.executeRead(DefaultJdbcRepositoryOperations.java:687)
		at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findOne(DefaultJdbcRepositoryOperations.java:286)
		at io.micronaut.data.runtime.intercept.DefaultFindPageInterceptor.intercept(DefaultFindPageInterceptor.java:57)
		at io.micronaut.data.intercept.DataIntroductionAdvice.intercept(DataIntroductionAdvice.java:81)
		at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:137)
		at io.micronaut.validation.ValidatingInterceptor.intercept(ValidatingInterceptor.java:143)
		at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:137)
		at com.example.SampleRepository$Intercepted.getSamplesPageProjection(Unknown Source)
		at com.example.Application.pageProjection(Application.java:64)
		at com.example.$Application$Definition$Exec.dispatch(Unknown Source)
		at io.micronaut.context.AbstractExecutableMethodsDefinition$DispatchedExecutableMethod.invoke(AbstractExecutableMethodsDefinition.java:378)
		at io.micronaut.context.DefaultBeanContext$4.invoke(DefaultBeanContext.java:594)
		at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:303)
		at io.micronaut.web.router.RouteMatch.execute(RouteMatch.java:111)
		at io.micronaut.http.context.ServerRequestContext.with(ServerRequestContext.java:103)
		at io.micronaut.http.server.RouteExecutor.lambda$executeRoute$14(RouteExecutor.java:659)
		at reactor.core.publisher.FluxDeferContextual.subscribe(FluxDeferContextual.java:49)
		at reactor.core.publisher.Flux.subscribe(Flux.java:8522)
		at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:426)
		at io.micronaut.reactive.reactor.instrument.ReactorSubscriber.onNext(ReactorSubscriber.java:57)
		at reactor.core.publisher.FluxHide$SuppressFuseableSubscriber.onNext(FluxHide.java:137)
		at reactor.core.publisher.Operators$ScalarSubscription.request(Operators.java:2398)
		at reactor.core.publisher.FluxHide$SuppressFuseableSubscriber.request(FluxHide.java:152)
		at reactor.core.publisher.FluxFlatMap$FlatMapMain.onSubscribe(FluxFlatMap.java:371)
		at io.micronaut.reactive.reactor.instrument.ReactorSubscriber.onSubscribe(ReactorSubscriber.java:50)
		at reactor.core.publisher.FluxHide$SuppressFuseableSubscriber.onSubscribe(FluxHide.java:122)
		at reactor.core.publisher.FluxJust.subscribe(FluxJust.java:68)
		at reactor.core.publisher.Flux.subscribe(Flux.java:8522)
		at io.micronaut.http.server.netty.RoutingInBoundHandler.handleRouteMatch(RoutingInBoundHandler.java:601)
		at io.micronaut.http.server.netty.RoutingInBoundHandler.channelRead0(RoutingInBoundHandler.java:457)
		at io.micronaut.http.server.netty.RoutingInBoundHandler.channelRead0(RoutingInBoundHandler.java:147)
		at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:99)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:102)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.micronaut.http.netty.stream.HttpStreamsHandler.channelRead(HttpStreamsHandler.java:224)
		at io.micronaut.http.netty.stream.HttpStreamsServerHandler.channelRead(HttpStreamsServerHandler.java:134)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:93)
		at io.netty.handler.codec.http.websocketx.extensions.WebSocketServerExtensionHandler.channelRead(WebSocketServerExtensionHandler.java:99)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
		at io.netty.handler.codec.MessageToMessageCodec.channelRead(MessageToMessageCodec.java:111)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:93)
		at io.netty.handler.codec.http.HttpServerKeepAliveHandler.channelRead(HttpServerKeepAliveHandler.java:64)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.handler.flow.FlowControlHandler.dequeue(FlowControlHandler.java:200)
		at io.netty.handler.flow.FlowControlHandler.channelRead(FlowControlHandler.java:162)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.CombinedChannelDuplexHandler$DelegatingChannelHandlerContext.fireChannelRead(CombinedChannelDuplexHandler.java:436)
		at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:336)
		at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:308)
		at io.netty.channel.CombinedChannelDuplexHandler.channelRead(CombinedChannelDuplexHandler.java:251)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:286)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
		at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:166)
		at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:788)
		at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:724)
		at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:650)
		at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:562)
		at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
		at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
		at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
		at java.base/java.lang.Thread.run(Thread.java:1589)
Caused by: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1345)
	at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1358)
	at com.mysql.cj.jdbc.ClientPreparedStatement.setString(ClientPreparedStatement.java:1714)
	at com.p6spy.engine.wrapper.PreparedStatementWrapper.setString(PreparedStatementWrapper.java:225)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setString(JdbcQueryStatement.java:206)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setString(JdbcQueryStatement.java:36)
	at io.micronaut.data.runtime.mapper.QueryStatement.setDynamic(QueryStatement.java:75)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setDynamic(JdbcQueryStatement.java:123)
	at io.micronaut.data.jdbc.mapper.JdbcQueryStatement.setDynamic(JdbcQueryStatement.java:36)

If you trick the runtime into executing the query by adding a redundant matching parameter to the count query, the query runs, but the parameters are not bound correctly and the paged data return 0 for total length. As a workaround for this, I was able to reorder the usage of the parameters in the countQuery to have them bound correctly and pass my unit tests.

Steps To Reproduce
  1. checkout https://github.com/safarmer/micronaut-data-pageable
  2. ./gradlew test

The sample has several variations of the same basic queries with an entity and a projection. The issue initially showed up in a DTO projection that has a derived field that uses a parameter that doesn't affect pagination. This was the closest thing I found so far that takes out some of the variance (i.e. reproduced with an entity and not a projection and a small query in getSamplesPageContrived).

Environment Information
  • Fedora 36
  • JDK 17
Example Application

https://github.com/safarmer/micronaut-data-pageable

Version

3.6.3 and 3.7.1

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.