-
Create a postgreql table with a jsonb column. For example:
create table house ( id serial primary key, categories jsonb not null ); -
Create a rdbc repository query which uses the operator '?| ' with spring boot version 3.0.5 For example:
... import org.springframework.data.r2dbc.repository.Query import org.springframework.stereotype.Repository ... @Repository ... @Query( """ select h.* from house h where h.categories ?| ARRAY[ :categories ] """ ) suspend fun findByCategoryIn(categories: List<String>): List<House> ...
Result: The query above causes a BadSqlGrammarException with spring boot 3.0.5 or 3.0.6
Expectation: This query should not cause a BadSqlGrammarException. The query works with spring boot 3.0.4
Stack trace:
executeMany; bad SQL grammar [
select h.* from house h
where h.categories ?| array[ $1 ]
]
org.springframework.r2dbc.BadSqlGrammarException: executeMany; bad SQL grammar [
select h.* from house a
where h.categories ?| array[ $1 ]
]
at app//org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:243)
at app//org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$8(DefaultDatabaseClient.java:151)
at app//reactor.core.publisher.Flux.lambda$onErrorMap$28(Flux.java:7134)
at app//reactor.core.publisher.Flux.lambda$onErrorResume$29(Flux.java:7187)
at app//reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94)
at app//reactor.core.publisher.FluxUsingWhen$UsingWhenSubscriber.deferredError(FluxUsingWhen.java:398)
at app//reactor.core.publisher.FluxUsingWhen$RollbackInner.onComplete(FluxUsingWhen.java:475)
at app//reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
at app//reactor.core.publisher.FluxPeek$PeekSubscriber.onComplete(FluxPeek.java:260)
at app//reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:209)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:209)
at app//reactor.pool.SimpleDequePool.maybeRecycleAndDrain(SimpleDequePool.java:531)
at app//reactor.pool.SimpleDequePool$QueuePoolRecyclerInner.onComplete(SimpleDequePool.java:761)
at app//reactor.core.publisher.Operators.complete(Operators.java:137)
at app//reactor.core.publisher.MonoEmpty.subscribe(MonoEmpty.java:46)
at app//reactor.core.publisher.Mono.subscribe(Mono.java:4485)
at app//reactor.pool.SimpleDequePool$QueuePoolRecyclerMono.subscribe(SimpleDequePool.java:873)
at app//reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)
at app//reactor.core.publisher.FluxPeek$PeekSubscriber.onComplete(FluxPeek.java:260)
at app//reactor.core.publisher.Operators.complete(Operators.java:137)
at app//reactor.core.publisher.MonoEmpty.subscribe(MonoEmpty.java:46)
at app//reactor.core.publisher.Mono.subscribe(Mono.java:4485)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)
at app//reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
at app//reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)
at app//reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89)
at app//reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onComplete(FluxHandleFuseable.java:238)
at app//reactor.core.publisher.MonoSupplier$MonoSupplierSubscription.request(MonoSupplier.java:148)
at app//reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.request(FluxHandleFuseable.java:259)
at app//reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onSubscribe(MonoIgnoreElements.java:72)
at app//reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onSubscribe(FluxHandleFuseable.java:163)
at app//reactor.core.publisher.MonoSupplier.subscribe(MonoSupplier.java:48)
at app//reactor.core.publisher.Mono.subscribe(Mono.java:4485)
at app//reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)
at app//reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
at app//reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)
at app//reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52)
at app//reactor.core.publisher.Mono.subscribe(Mono.java:4485)
at app//reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:103)
at app//reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onError(MonoIgnoreElements.java:84)
at app//reactor.core.publisher.FluxMap$MapSubscriber.onError(FluxMap.java:134)
at app//reactor.core.publisher.FluxFilter$FilterSubscriber.onError(FluxFilter.java:157)
at app//reactor.core.publisher.FluxFilter$FilterConditionalSubscriber.onError(FluxFilter.java:291)
at app//reactor.core.publisher.FluxMap$MapConditionalSubscriber.onError(FluxMap.java:265)
at app//reactor.core.publisher.Operators.error(Operators.java:198)
at app//reactor.core.publisher.MonoError.subscribe(MonoError.java:53)
at app//reactor.core.publisher.MonoDeferContextual.subscribe(MonoDeferContextual.java:55)
at app//reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)
at app//reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52)
at app//reactor.core.publisher.Mono.subscribe(Mono.java:4485)
at app//reactor.core.publisher.FluxUsingWhen$UsingWhenSubscriber.onError(FluxUsingWhen.java:364)
at app//reactor.core.publisher.FluxFlatMap$FlatMapMain.checkTerminated(FluxFlatMap.java:843)
at app//reactor.core.publisher.FluxFlatMap$FlatMapMain.drainLoop(FluxFlatMap.java:609)
at app//reactor.core.publisher.FluxFlatMap$FlatMapMain.drain(FluxFlatMap.java:589)
at app//reactor.core.publisher.FluxFlatMap$FlatMapMain.innerError(FluxFlatMap.java:864)
at app//reactor.core.publisher.FluxFlatMap$FlatMapInner.onError(FluxFlatMap.java:991)
at app//reactor.core.publisher.FluxHandle$HandleSubscriber.onError(FluxHandle.java:212)
at app//reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onError(MonoFlatMapMany.java:255)
at app//reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:200)
at app//reactor.core.publisher.FluxFilterFuseable$FilterFuseableConditionalSubscriber.onNext(FluxFilterFuseable.java:337)
at app//reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107)
at app//reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
at app//reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
at app//io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onNext(FluxDiscardOnCancel.java:91)
at app//reactor.core.publisher.FluxDoFinally$DoFinallySubscriber.onNext(FluxDoFinally.java:113)
at app//reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:128)
at app//reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:814)
at app//reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:739)
at app//reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:161)
at app//io.r2dbc.postgresql.client.ReactorNettyClient$Conversation.emit(ReactorNettyClient.java:687)
at app//io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.emit(ReactorNettyClient.java:939)
at app//io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:813)
at app//io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:719)
at app//reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:128)
at app//reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
at app//reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224)
at app//reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224)
at app//reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:292)
at app//reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:401)
at app//reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:411)
at app//reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:113)
at app//io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
at app//io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
at app//io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
at app//io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
at app//io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333)
at app//io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:454)
at app//io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290)
at app//io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
at app//io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
at app//io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
at app//io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
at app//io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
at app//io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
at app//io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
at app//io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:800)
at app//io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:499)
at app//io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:397)
at app//io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:997)
at app//io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
at app//io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.base@17.0.3/java.lang.Thread.run(Thread.java:833)
Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42883] operator does not exist: jsonb ?| jsonb[]
at app//io.r2dbc.postgresql.ExceptionFactory.createException(ExceptionFactory.java:96)
at app//io.r2dbc.postgresql.ExceptionFactory.createException(ExceptionFactory.java:65)
at app//io.r2dbc.postgresql.ExceptionFactory.handleErrorResponse(ExceptionFactory.java:132)
at app//reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:178)
... 43 more
Comment From: mhalbritter
Hey. You haven't changed the PostgreSQL version, only Boot? Do you have a sample with which we can reproduce this issue? Ideally with docker compose files for the postgres version you're using?
Comment From: amx98
We're useing postgresql 13.4. But in the meantime it doesn't seem to be a spring boot issue. I found out that a json converter was attached to the R2dbcConfiguration, which converts the List
Comment From: wilkinsona
Thanks for following up. The only change that I can think of that may be relevant here is the Spring Data upgrade. Other versions, such as R2DBC and the Postgres R2DBC driver did not change in 3.0.5. You may want to raise this with the Spring Data team.