Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Pageable JDBC query with extra parameters not used in countQuery fails #1773

Open
safarmer opened this issue Sep 29, 2022 · 0 comments · May be fixed by #1883
Open

Pageable JDBC query with extra parameters not used in countQuery fails #1773

safarmer opened this issue Sep 29, 2022 · 0 comments · May be fixed by #1883
Assignees
@radovanradic
Labels
type: bug Something isn't working

Comments

@safarmer
Copy link
Contributor

safarmer commented Sep 29, 2022 •
edited

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
@safarmer safarmer changed the title Pageable query with extra parameters not used in countQuery fails Pageable JDBC query with extra parameters not used in countQuery fails Sep 29, 2022
@radovanradic radovanradic linked a pull request Nov 25, 2022 that will close this issue
Open
@radovanradic radovanradic added the type: bug Something isn't working label Nov 25, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@radovanradic radovanradic

Labels
type: bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Fix count query with different parameters than main query micronaut-projects/micronaut-data
2 participants
@safarmer @radovanradic