Unable to use result set streaming on version 1.6.3 with Hibernate

[dsarlo-viso]

Description

TypedQuery<View> query = viewBuilder.getQuery();
return query.getResultStream();

Using the above snippet works on version 1.6.0, but since moving to 1.6.3 (this is required on our end), we receive the following exception.

org.hibernate.exception.GenericJDBCException: could not advance using next()
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.internal.ScrollableResultsImpl.convert(ScrollableResultsImpl.java:71)
	at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:106)
	at org.hibernate.query.internal.ScrollableResultsIterator.hasNext(ScrollableResultsIterator.java:33)
	at java.base/java.util.Spliterators$IteratorSpliterator.tryAdvance(Spliterators.java:1811)
	at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.lambda$initPartialTraversalState$0(StreamSpliterators.java:294)
	at java.base/java.util.stream.StreamSpliterators$AbstractWrappingSpliterator.fillBuffer(StreamSpliterators.java:206)
	at java.base/java.util.stream.StreamSpliterators$AbstractWrappingSpliterator.doAdvance(StreamSpliterators.java:161)
	at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.tryAdvance(StreamSpliterators.java:300)
	at java.base/java.util.Spliterators$1Adapter.hasNext(Spliterators.java:681)
	at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:132)
	at reactor.core.publisher.FluxStream.subscribe(FluxStream.java:71)
	at reactor.core.publisher.FluxUsing.subscribe(FluxUsing.java:102)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8185)
	at reactor.core.publisher.FluxConcatArray$ConcatArraySubscriber.onComplete(FluxConcatArray.java:208)
	at reactor.core.publisher.FluxConcatArray.subscribe(FluxConcatArray.java:80)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8185)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onNext(MonoFlatMapMany.java:195)
	at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1815)
	at reactor.core.publisher.MonoCompletionStage.lambda$subscribe$0(MonoCompletionStage.java:82)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
	at java.base/java.util.concurrent.CompletableFuture$UniWhenComplete.tryFire(CompletableFuture.java:837)
	at java.base/java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1705)
	at org.springframework.security.concurrent.DelegatingSecurityContextRunnable.run(DelegatingSecurityContextRunnable.java:82)
	at io.github.jhipster.async.ExceptionHandlingAsyncTaskExecutor.lambda$createWrappedRunnable$1(ExceptionHandlingAsyncTaskExecutor.java:78)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: This ResultSet is closed.
	at org.postgresql.jdbc.PgResultSet.checkClosed(PgResultSet.java:2905)
	at org.postgresql.jdbc.PgResultSet.next(PgResultSet.java:1924)
	at com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java)
	at org.hibernate.internal.ScrollableResultsImpl.next(ScrollableResultsImpl.java:101)
	... 26 common frames omitted

The temporary workaround provided by @jwgmeligmeyling worked, which is great, but we are relying on streaming here because these result sets are large.

Here is a screenshot of the call stack:

Looks like it is delegating down to hibernate's QueryImpl on 1.6.3.

Expected behavior

This exception does not occur and we get a stream of results, just as we did on 1.6.0

Actual behavior

Hibernate exception thrown - org.hibernate.exception.GenericJDBCException: could not advance using next()

Steps to reproduce

I have created this test project - https://github.com/dsarlo-viso/blaze-web-implementation-issues - but I am unable to reproduce the issue here. Looking to get some insight in this thread into what could have possibly changed between 1.6.0 and 1.6.3 that this is now a problem for us.

Environment

Version: 1.6.3
JPA-Provider: Hibernate 5.4.29.Final (Spring Data JPA 2.4.4)
DBMS: PostgreSQL 9.6
Application Server: Spring Boot 2.4.4

[jwgmeligmeyling]

Steaming was only released in 1.6.2. Are you sure that under 1.6.0 you were actually streaming the results?

[jason-visotrust]

Well, it was calling TypedQuery.getResultStream() and was working to return a Stream of EntityViews.

[jwgmeligmeyling]

We wrap TypedQuery in a custom implementation that would do the entity view wrapping, so I am assuming just the default stream implementation was used prior (which falls back on calling getResultList().stream())

[beikov]

Interesting that you use this in a JHipster application. Maybe you want to help us with a sample application here? #332

To me, this looks like an issue with the Hibernate session management. It seems you are closing the Hibernate session at some point, before you are done with scrolling/streaming. Before 1.6.2 the result was fully fetched eagerly, so that's why you didn't have problems so far. I don't know enough about JHipster unfortunately to help you with this, but you need to keep the Hibernate session open until the request finishes.

[beikov]

Maybe you can open a Hibernate session explicitly and add a close handler to the result stream via Stream#onClose

[jason-visotrust]

That's interesting... I'll have to look into how sessions are managed with WebFlux and @async

[jason-visotrust]

I think this was part of the issue. I'm now opening my own EntityManager in the service and passing it along, then closing when the Stream closes, but now I'm getting this:

java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class com.visotrust.viso.service.riskinsight.SlimRiskAssessment ([Ljava.lang.Object; is in module java.base of loader 'bootstrap'; com.visotrust.viso.service.riskinsight.SlimRiskAssessment is in unnamed module of loader 'app')
at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:386)

This is when my flatMap() is trying to accumulate the SlimRiskAssessment objects coming from the stream, but instead of getting an EntityView SlimRiskAssessment I'm getting an Object[] of the values that should go into it.

[beikov]

Ahh, now this is an actual issue with Blaze-Persistence, although I am a bit surprised as the transformation should happen as you can see in https://github.com/Blazebit/blaze-persistence/blob/master/core/impl/src/main/java/com/blazebit/persistence/impl/query/ObjectBuilderTypedQuery.java#L80

Could you maybe try to debug to help us understand what is going on?

[jason-visotrust]

Can you point me to where I should drop a breakpoint? I'm building a Flux from the stream returned from query.getResultStream() like this:

private Flux getRiskAssessmentsFlux(@NotNull Long orgId, Optional<List> maybeDrIds, EntityManager em) {
try {
return Flux.using(
() -> riskInsightRepository.getRisks(orgId, maybeDrIds, em),
Flux::fromStream,
stream -> {
em.close();
stream.close();
}
);
} catch (Exception e) {
em.close();
throw e;
}
}

and then passing that flux directly into a flatmap which is where it's failing.

[beikov]

How does riskInsightRepository.getRisks look like? Do you see the method I referred to before being called? It's possible that the entity view ObjectBuilder simply does nothing on build and only works on buildList. Can you confirm that all of the code is called and that it simply no-ops?

[jason-visotrust]

ObjectBuilderTypedQuery.getResultStream() is called.
public X apply(X tuple) { if (tuple instanceof Object[]) { return builder.build((Object[]) tuple); } else { return builder.build(new Object[]{tuple}); } }
In this part, type is an instance of Object[] and it calls the first, which returns an Object[].

The objectBuilder objectInstantiator has the correct constructor for the Impl class built for my EntityView

[jason-visotrust]

`public Stream getRisks(@NotNull Long senderOrgId, Optional<List> maybeDrIds, EntityManager em) {
CriteriaBuilder criteriaBuilder = cbf.create(em, RiskAssessment.class);
criteriaBuilder.where("diligenceRequest.sender.id").eq(senderOrgId).orderByAsc("createdDate");
if (maybeDrIds.isPresent()) {
List drIds = maybeDrIds.get();
criteriaBuilder.where("diligenceRequest.id").in(drIds);
}
CriteriaBuilder riskAssessmentBuilder = evm.applySetting(EntityViewSetting.create(SlimRiskAssessment.class), criteriaBuilder);
TypedQuery query = riskAssessmentBuilder.getQuery();
query.setHint(QueryHints.READ_ONLY, true);

    return query.getResultStream();
}`

[jason-visotrust]

I think it's this in the ChainingObjectBuilder:
public T build(Object[] tuple) { return (T) tuple; }

It's just returning the Object[] without transforming it

[beikov]

Ok, so I guess that the delegate builder probably then does a no-op at some point as we don't support "incremental" object building yet since we require some de-duplication to happen right now in bulk fashion. I can look into this, but for now, you will have to live without streaming unfortunately.

[jason-visotrust]

Okay, gotcha. I guess we were living without it before. Thanks for taking a look.