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

SQL Query fails with no space in between CAST and named parameter #820

Closed
ghillert opened this issue Nov 19, 2020 · 1 comment
Closed

SQL Query fails with no space in between CAST and named parameter #820

ghillert opened this issue Nov 19, 2020 · 1 comment
Labels
type: bug Something isn't working

Comments

@ghillert
Copy link

With Micronaut Data version 2.2.0 I have the following SQL Query that works:

@Query(
    value = "SELECT * from Plants p WHERE st_dwithin(p.location, CAST( :location AS geometry), :radius, true) = true")
List<Plant> getPlantsWithinRadius(@TypeDef(type = DataType.STRING) Point location, double radius);

It creates correct SQL and logs:

12:26:06.673 [default-nioEventLoopGroup-1-2] DEBUG io.micronaut.data.query - Executing Query: SELECT * from Plants p WHERE st_dwithin(p.location, CAST( ? AS geometry), ?, true) = true

However, if by accident I remove the space between CAST( and :location, the query fails. Note that the generated SQL as per the log is no longer valid.

12:30:35.146 [default-nioEventLoopGroup-1-2] DEBUG io.micronaut.data.query - Executing Query: SELECT * from Plants p WHERE st_dwithin(p.location, CAST ? AS geometry), ?, true) = true
12:30:39.774 [default-nioEventLoopGroup-1-2] ERROR i.m.h.s.netty.RoutingInBoundHandler - Unexpected error occurred: SQL Error executing Query: ERROR: syntax error at or near "$1"
  Position: 58
io.micronaut.data.exceptions.DataAccessException: SQL Error executing Query: ERROR: syntax error at or near "$1"
  Position: 58
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findStream(DefaultJdbcRepositoryOperations.java:297)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.lambda$findAll$5(DefaultJdbcRepositoryOperations.java:394)
	at io.micronaut.transaction.support.AbstractSynchronousTransactionManager.executeRead(AbstractSynchronousTransactionManager.java:155)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findAll(DefaultJdbcRepositoryOperations.java:392)
	at io.micronaut.data.runtime.intercept.DefaultFindAllInterceptor.intercept(DefaultFindAllInterceptor.java:52)
	at io.micronaut.data.runtime.intercept.DefaultFindAllInterceptor.intercept(DefaultFindAllInterceptor.java:36)
	at io.micronaut.data.intercept.DataIntroductionAdvice.intercept(DataIntroductionAdvice.java:80)
	at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:82)
	at io.micronaut.validation.ValidatingInterceptor.intercept(ValidatingInterceptor.java:139)
	at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:82)
	at com.hillert.s1.plants.dao.PlantRepository$Intercepted.getPlantsWithinRadius(Unknown Source)
	at com.hillert.s1.plants.service.impl.DefaultPlantService.getPlantsWithinRadius(DefaultPlantService.java:66)
	at com.hillert.s1.plants.service.impl.$DefaultPlantServiceDefinition$Intercepted.$$access1(Unknown Source)
	at com.hillert.s1.plants.service.impl.$DefaultPlantServiceDefinition$Intercepted$$proxy1.invokeInternal(Unknown Source)
	at io.micronaut.context.AbstractExecutableMethod.invoke(AbstractExecutableMethod.java:146)
	at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:73)
	at io.micronaut.transaction.interceptor.TransactionalInterceptor.intercept(TransactionalInterceptor.java:134)
	at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:82)
	at com.hillert.s1.plants.service.impl.$DefaultPlantServiceDefinition$Intercepted.getPlantsWithinRadius(Unknown Source)
	at com.hillert.s1.plants.controller.PlantController.getSinglePlant(PlantController.java:121)
	at com.hillert.s1.plants.controller.$PlantControllerDefinition$$exec4.invokeInternal(Unknown Source)
	at io.micronaut.context.AbstractExecutableMethod.invoke(AbstractExecutableMethod.java:146)
	at io.micronaut.context.DefaultBeanContext$4.invoke(DefaultBeanContext.java:474)
	at io.micronaut.web.router.AbstractRouteMatch.execute(AbstractRouteMatch.java:312)
	at io.micronaut.web.router.RouteMatch.execute(RouteMatch.java:118)
	at io.micronaut.http.server.netty.RoutingInBoundHandler.lambda$buildResultEmitter$10(RoutingInBoundHandler.java:1363)
	at io.reactivex.internal.operators.flowable.FlowableDefer.subscribeActual(FlowableDefer.java:35)
	at io.reactivex.Flowable.subscribe(Flowable.java:14918)
	at io.reactivex.Flowable.subscribe(Flowable.java:14865)
	at io.micronaut.http.server.context.ServerRequestContextFilter.lambda$doFilter$0(ServerRequestContextFilter.java:62)
	at io.reactivex.internal.operators.flowable.FlowableFromPublisher.subscribeActual(FlowableFromPublisher.java:29)
	at io.reactivex.Flowable.subscribe(Flowable.java:14918)
	at io.reactivex.Flowable.subscribe(Flowable.java:14868)
	at io.micronaut.http.server.netty.RoutingInBoundHandler.lambda$buildExecutableRoute$6(RoutingInBoundHandler.java:1068)
	at io.micronaut.web.router.DefaultUriRouteMatch$1.execute(DefaultUriRouteMatch.java:80)
	at io.micronaut.web.router.RouteMatch.execute(RouteMatch.java:118)
	at io.micronaut.http.server.netty.RoutingInBoundHandler.handleRouteMatch(RoutingInBoundHandler.java:693)
	at io.micronaut.http.server.netty.RoutingInBoundHandler.channelRead0(RoutingInBoundHandler.java:549)
	at io.micronaut.http.server.netty.RoutingInBoundHandler.channelRead0(RoutingInBoundHandler.java:144)
	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:197)
	at io.micronaut.http.netty.stream.HttpStreamsServerHandler.channelRead(HttpStreamsServerHandler.java:121)
	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:324)
	at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:296)
	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:714)
	at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:650)
	at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:576)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493)
	at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
	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:834)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 58
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findStream(DefaultJdbcRepositoryOperations.java:290)
	... 98 common frames omitted

Also, is there a way to make Postgres casts using the :: syntax work? The following does not seem to work either:

@Query(value = "SELECT * from Plants p WHERE st_dwithin(p.location, :location \\:\\:geometry), :radius, true) = true")
@graemerocher graemerocher added the type: bug Something isn't working label Nov 20, 2020
@graemerocher
Copy link
Contributor

Corrected by fix for #823

@renovate renovate bot mentioned this issue Feb 23, 2023
Closed
1 task
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
type: bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@graemerocher @ghillert