search

jOOQ / jOOQ

jOOQ is the best way to write SQL in Java
https://www.jooq.org
Other
6.17k stars 1.21k forks source link

R2DBC `array_agg` with `jsonb` column `ClassCastException` #16933

Open MarcusDunn opened 4 months ago

MarcusDunn commented 4 months ago

Expected behavior

DSL.using(connectionFactory)
           .select(DSL.arrayAgg(TRANSCRIPT_UTTERANCES.SENTIMENT))
           .from(TRANSCRIPTS)
           .leftJoin(TRANSCRIPT_UTTERANCES)
           .on(TRANSCRIPT_UTTERANCES.TRANSCRIPT_ID.eq(TRANSCRIPTS.TRANSCRIPT_ID))
           .groupBy(TRANSCRIPTS.TRANSCRIPT_ID)
           .asFlow()
           .toList()

does not throw.

Actual behavior

it throws.

Stacktrace 
SQL [select array_agg("public"."transcript_utterances"."sentiment") from "public"."transcripts" left outer join "public"."transcript_utterances" on "public"."transcript_utterances"."transcript_id" = "public"."transcripts"."transcript_id" group by "public"."transcripts"."transcript_id"]; Error while reading field: array_agg("public"."transcript_utterances"."sentiment"), at JDBC index: 1
org.jooq.exception.DataAccessException: SQL [select array_agg("public"."transcript_utterances"."sentiment") from "public"."transcripts" left outer join "public"."transcript_utterances" on "public"."transcript_utterances"."transcript_id" = "public"."transcripts"."transcript_id" group by "public"."transcripts"."transcript_id"]; Error while reading field: array_agg("public"."transcript_utterances"."sentiment"), at JDBC index: 1
    at org.jooq.impl.Tools.translate(Tools.java:3607)
    at org.jooq.impl.Tools.translate(Tools.java:3595)
    at org.jooq.impl.Tools.translate(Tools.java:3571)
    at org.jooq.impl.R2DBC$AbstractResultSubscriber.lambda$onError$0(R2DBC.java:285)
    at org.jooq.impl.Internal$1.onComplete(Internal.java:759)
    at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:209)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:238)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)
    at reactor.core.publisher.FluxPeek$PeekSubscriber.onComplete(FluxPeek.java:260)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:209)
    at reactor.core.publisher.Operators.complete(Operators.java:137)
    at reactor.netty.FutureMono.doSubscribe(FutureMono.java:122)
    at reactor.netty.FutureMono$ImmediateFutureMono.subscribe(FutureMono.java:83)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:240)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onComplete(MonoIgnoreThen.java:203)
    at reactor.core.publisher.MonoPeekTerminal$MonoTerminalPeekSubscriber.onComplete(MonoPeekTerminal.java:299)
    at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89)
    at reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.onComplete(FluxConcatMapNoPrefetch.java:240)
    at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.onComplete(FluxPeekFuseable.java:277)
    at reactor.core.publisher.Operators$ScalarSubscription.request(Operators.java:2547)
    at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.request(FluxPeekFuseable.java:144)
    at reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.request(FluxConcatMapNoPrefetch.java:336)
    at reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onSubscribe(MonoIgnoreElements.java:72)
    at reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.onSubscribe(FluxConcatMapNoPrefetch.java:164)
    at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.onSubscribe(FluxPeekFuseable.java:178)
    at reactor.core.publisher.FluxJust.subscribe(FluxJust.java:68)
    at reactor.core.publisher.Mono.subscribe(Mono.java:4495)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)
    at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
    at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:53)
    at reactor.core.publisher.Mono.subscribe(Mono.java:4495)
    at reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:263)
    at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
    at reactor.core.publisher.Mono.subscribe(Mono.java:4495)
    at org.jooq.impl.R2DBC$AbstractNonBlockingSubscription.lambda$cancel0$4(R2DBC.java:660)
    at java.base/java.util.concurrent.atomic.AtomicReference.updateAndGet(AtomicReference.java:210)
    at org.jooq.impl.R2DBC$AbstractNonBlockingSubscription.cancel0(R2DBC.java:644)
    at org.jooq.impl.R2DBC$AbstractSubscription.complete(R2DBC.java:210)
    at org.jooq.impl.R2DBC$AbstractResultSubscriber.complete(R2DBC.java:300)
    at org.jooq.impl.R2DBC$AbstractResultSubscriber.onError(R2DBC.java:285)
    at org.jooq.impl.R2DBC$ResultSubscriber.lambda$onNext$1(R2DBC.java:368)
    at io.r2dbc.postgresql.PostgresqlResult.lambda$map$2(PostgresqlResult.java:129)
    at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:179)
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:670)
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drain(FluxWindowPredicate.java:748)
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.onNext(FluxWindowPredicate.java:790)
    at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.onNext(FluxWindowPredicate.java:268)
    at io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onNext(FluxDiscardOnCancel.java:91)
    at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107)
    at reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:878)
    at reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:803)
    at reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:161)
    at io.r2dbc.postgresql.client.ReactorNettyClient$Conversation.emit(ReactorNettyClient.java:684)
    at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.emit(ReactorNettyClient.java:936)
    at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:810)
    at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:716)
    at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:129)
    at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
    at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224)
    at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224)
    at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:294)
    at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:403)
    at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:426)
    at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:114)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
    at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
    at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
    at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333)
    at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:454)
    at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
    at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
    at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
    at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
    at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:800)
    at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:509)
    at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:407)
    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:1583)
Caused by: java.sql.SQLException: Error while reading field: array_agg("public"."transcript_utterances"."sentiment"), at JDBC index: 1
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.setValue(CursorImpl.java:1592)
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.apply(CursorImpl.java:1518)
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.apply(CursorImpl.java:1433)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
    at org.jooq.impl.R2DBC$ResultSubscriber.lambda$onNext$1(R2DBC.java:362)
    ... 44 more
Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class [Ljava.lang.Object; (java.lang.String and [Ljava.lang.Object; are in module java.base of loader 'bootstrap')
    at org.jooq.impl.R2DBC$R2DBCResultSet.getArray(R2DBC.java:1311)
    at org.jooq.impl.DefaultBinding$DefaultArrayBinding.pgGetArray(DefaultBinding.java:1472)
    at org.jooq.impl.DefaultBinding$DefaultArrayBinding.get0(DefaultBinding.java:1426)
    at org.jooq.impl.DefaultBinding$DefaultArrayBinding.get0(DefaultBinding.java:1280)
    at org.jooq.impl.DefaultBinding$InternalBinding.get(DefaultBinding.java:1129)
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.setValue(CursorImpl.java:1582)
    ... 48 more

Error while reading field: array_agg("public"."transcript_utterances"."sentiment"), at JDBC index: 1
java.sql.SQLException: Error while reading field: array_agg("public"."transcript_utterances"."sentiment"), at JDBC index: 1
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.setValue(CursorImpl.java:1592)
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.apply(CursorImpl.java:1518)
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.apply(CursorImpl.java:1433)
    at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:144)
    at org.jooq.impl.R2DBC$ResultSubscriber.lambda$onNext$1(R2DBC.java:362)
    at io.r2dbc.postgresql.PostgresqlResult.lambda$map$2(PostgresqlResult.java:129)
    at reactor.core.publisher.FluxHandleFuseable$HandleFuseableSubscriber.onNext(FluxHandleFuseable.java:179)
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drainRegular(FluxWindowPredicate.java:670)
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.drain(FluxWindowPredicate.java:748)
    at reactor.core.publisher.FluxWindowPredicate$WindowFlux.onNext(FluxWindowPredicate.java:790)
    at reactor.core.publisher.FluxWindowPredicate$WindowPredicateMain.onNext(FluxWindowPredicate.java:268)
    at io.r2dbc.postgresql.util.FluxDiscardOnCancel$FluxDiscardOnCancelSubscriber.onNext(FluxDiscardOnCancel.java:91)
    at reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107)
    at reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:878)
    at reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:803)
    at reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:161)
    at io.r2dbc.postgresql.client.ReactorNettyClient$Conversation.emit(ReactorNettyClient.java:684)
    at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.emit(ReactorNettyClient.java:936)
    at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:810)
    at io.r2dbc.postgresql.client.ReactorNettyClient$BackendMessageSubscriber.onNext(ReactorNettyClient.java:716)
    at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:129)
    at reactor.core.publisher.FluxPeekFuseable$PeekConditionalSubscriber.onNext(FluxPeekFuseable.java:854)
    at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224)
    at reactor.core.publisher.FluxMap$MapConditionalSubscriber.onNext(FluxMap.java:224)
    at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:294)
    at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:403)
    at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:426)
    at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:114)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
    at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
    at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:346)
    at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:333)
    at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:454)
    at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:290)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:444)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
    at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:412)
    at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:440)
    at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:420)
    at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
    at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:800)
    at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:509)
    at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:407)
    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:1583)
Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class [Ljava.lang.Object; (java.lang.String and [Ljava.lang.Object; are in module java.base of loader 'bootstrap')
    at org.jooq.impl.R2DBC$R2DBCResultSet.getArray(R2DBC.java:1311)
    at org.jooq.impl.DefaultBinding$DefaultArrayBinding.pgGetArray(DefaultBinding.java:1472)
    at org.jooq.impl.DefaultBinding$DefaultArrayBinding.get0(DefaultBinding.java:1426)
    at org.jooq.impl.DefaultBinding$DefaultArrayBinding.get0(DefaultBinding.java:1280)
    at org.jooq.impl.DefaultBinding$InternalBinding.get(DefaultBinding.java:1129)
    at org.jooq.impl.CursorImpl$CursorRecordInitialiser.setValue(CursorImpl.java:1582)
    ... 48 more

Steps to reproduce the problem

DSL.using(connectionFactory)
           .select(DSL.arrayAgg(TRANSCRIPT_UTTERANCES.SENTIMENT))
           .from(TRANSCRIPTS)
           .leftJoin(TRANSCRIPT_UTTERANCES)
           .on(TRANSCRIPT_UTTERANCES.TRANSCRIPT_ID.eq(TRANSCRIPTS.TRANSCRIPT_ID))
           .groupBy(TRANSCRIPTS.TRANSCRIPT_ID)
           .asFlow()
           .toList()

results the following SQL (as logged by io.r2dbc.postgresql.QUERY)

select array_agg("public"."transcript_utterances"."sentiment")
from "public"."transcripts"
         left outer join "public"."transcript_utterances"
                         on "public"."transcript_utterances"."transcript_id" = "public"."transcripts"."transcript_id"
group by "public"."transcripts"."transcript_id"
DDL 
create table transcript_utterances
(
    id            bigint generated by default as identity
        primary key,
    transcript_id integer                  not null
        constraint fk_transcript_thoughts_transcript
            references transcripts,
    text          text                     not null,
    speaker       text                     not null,
    timestamp     timestamp with time zone not null,
    sentiment     jsonb
);

create table transcripts
(
    transcript    jsonb                    not null,
    transcript_id integer generated by default as identity
        primary key,
    is_deleted    boolean default false    not null,
    start         timestamp with time zone not null,
    "end"         timestamp with time zone,
    reason        text,
    contact_id    text
);

Extra notes from debugging on my end

jOOQ Version

3.19.10

Database product and version

PostgreSQL 16.2 (Debian 16.2-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Java Version

21 corretto

JDBC / R2DBC driver name and version (include name if unofficial driver)

org.postgresql.r2dbc-postgresql:1.0.4.RELEASE

AND

org.postgresql.r2dbc-postgresql:1.0.5.RELEASE

MarcusDunn commented 4 months ago

I think this is the same issue as https://github.com/jOOQ/jOOQ/issues/15548 / https://github.com/jOOQ/jOOQ/issues/13590. Wild I have not run into this before.

lukaseder commented 4 months ago

Thanks for your report. Yes, #13590 lists lack of array support so far in R2DBC.