Pageable JDBC query with extra parameters not used in countQuery fails #1773
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
- checkout https://github.com/safarmer/micronaut-data-pageable
- ./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