R2DBC treating warning as exception

[rathoreamrsingh]

Hi,

I am using R2DBC with oracle, where I am trying to get some details from a view.
This view is having some warning while executing it which is not an exception but R2DBC is treating that as exception and is failing.

If I run the exact same query with limit of 9 or less records it works fine.
I tried this view with Spring data JPA and there it works perfectly fine.

Database used in this case is Oracle

Please have a look on this below log for more details.

2022-09-09 15:03:51,876 INFO  [reactor-http-nio-2] com.gic.eagle.txnHld.handler.VWODAHoldingEglHandler: trying to get view data from ODA RE from [01Jan1991] to [09Jan2022]
2022-09-09 15:04:09,993 DEBUG [ForkJoinPool.commonPool-worker-3] org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec: Executing SQL statement [SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate]
2022-09-09 15:04:10,384 ERROR [ForkJoinPool.commonPool-worker-3] reactor.util.Loggers$Slf4JLogger: Operator called default onErrorDropped
reactor.core.Exceptions$StaticThrowable: Operator has been terminated
2022-09-09 15:04:10,411 ERROR [ForkJoinPool.commonPool-worker-3] com.gic.eagle.txnHld.handler.TxnHldDataTransHandler: Error [executeMany; SQL [SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate]; Warning: execution completed with warning; nested exception is oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: [17110] [99999] Warning: execution completed with warning]
org.springframework.r2dbc.UncategorizedR2dbcException: executeMany; SQL [SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate]; Warning: execution completed with warning; nested exception is oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: [17110] [99999] Warning: execution completed with warning
	at org.springframework.r2dbc.connection.ConnectionFactoryUtils.convertR2dbcException(ConnectionFactoryUtils.java:238)
	at org.springframework.r2dbc.core.DefaultDatabaseClient.lambda$inConnectionMany$8(DefaultDatabaseClient.java:147)
	at reactor.core.publisher.Flux.lambda$onErrorMap$29(Flux.java:6943)
	at reactor.core.publisher.Flux.lambda$onErrorResume$30(Flux.java:6996)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94)
	at reactor.core.publisher.FluxUsingWhen$UsingWhenSubscriber.deferredError(FluxUsingWhen.java:398)
	at reactor.core.publisher.FluxUsingWhen$RollbackInner.onComplete(FluxUsingWhen.java:475)
	at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058)
	at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:102)
	at reactor.core.publisher.MonoNext$NextSubscriber.onComplete(MonoNext.java:102)
	at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.onComplete(AsyncLock.java:497)
	at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123)
	at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058)
	at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onComplete(FlowAdapters.java:221)
	at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitComplete(CompletionStageUtil.java:805)
	at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.subscribeToBatch(CompletionStageUtil.java:622)
	at oracle.jdbc.internal.CompletionStageUtil$BatchItemPublisher.lambda$subscribe$0(CompletionStageUtil.java:593)
	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.complete(CompletableFuture.java:2073)
	at oracle.jdbc.driver.T4CTTIfun.lambda$doRPCAsync$0(T4CTTIfun.java:348)
	at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:474)
	at oracle.jdbc.driver.RestrictedLock.lambda$runUnrestricted$0(RestrictedLock.java:428)
	at oracle.jdbc.driver.RestrictedLock.callUnrestricted(RestrictedLock.java:447)
	at oracle.jdbc.driver.RestrictedLock.runUnrestricted(RestrictedLock.java:427)
	at oracle.jdbc.driver.PhysicalConnection.lambda$initializeAsyncExecutor$4(PhysicalConnection.java:1270)
	at java.base/java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1426)
	at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:290)
	at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1020)
	at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1656)
	at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1594)
	at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:183)
Caused by: oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: Warning: execution completed with warning
	at oracle.r2dbc.impl.OracleR2dbcExceptions.toR2dbcException(OracleR2dbcExceptions.java:217)
	at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
	at java.base/java.util.stream.Stream$2.tryAdvance(Stream.java:1301)
	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:133)
Caused by: oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcException: Warning: execution completed with warning

	at reactor.core.publisher.FluxStream.subscribe(FluxStream.java:71)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8466)
	at reactor.core.publisher.FluxConcatArray$ConcatArraySubscriber.onComplete(FluxConcatArray.java:258)
	at reactor.core.publisher.FluxConcatArray.subscribe(FluxConcatArray.java:78)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8466)
	at reactor.core.publisher.FluxConcatArray$ConcatArrayDelayErrorSubscriber.onComplete(FluxConcatArray.java:443)
	at reactor.core.publisher.FluxConcatArray.subscribe(FluxConcatArray.java:73)
	at reactor.core.publisher.InternalFluxOperator.subscribe(InternalFluxOperator.java:62)
	at reactor.core.publisher.FluxDefer.subscribe(FluxDefer.java:54)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8466)
	at reactor.core.publisher.FluxFlatMap$FlatMapMain.onNext(FluxFlatMap.java:426)
	at reactor.core.publisher.FluxOnAssembly$OnAssemblySubscriber.onNext(FluxOnAssembly.java:539)
	at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250)
	at reactor.core.publisher.FluxUsingWhen$UsingWhenSubscriber.onNext(FluxUsingWhen.java:345)
	at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
	at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:200)
	at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
	at reactor.core.publisher.FluxConcatArray$ConcatArraySubscriber.onNext(FluxConcatArray.java:201)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:250)
	at reactor.core.publisher.Operators$ScalarSubscription.request(Operators.java:2398)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onSubscribeInner(MonoFlatMapMany.java:150)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onNext(MonoFlatMapMany.java:189)
	at reactor.core.publisher.MonoCreate$DefaultMonoSink.success(MonoCreate.java:172)
	at oracle.r2dbc.impl.AsyncLock.lambda$get$2(AsyncLock.java:163)
	at oracle.r2dbc.impl.AsyncLock.unlock(AsyncLock.java:122)
	at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.terminate(AsyncLock.java:510)
	at oracle.r2dbc.impl.AsyncLock$UsingConnectionSubscriber.onComplete(AsyncLock.java:496)
	at reactor.core.publisher.StrictSubscriber.onComplete(StrictSubscriber.java:123)
	at reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2058)
	at org.reactivestreams.FlowAdapters$FlowToReactiveSubscriber.onComplete(FlowAdapters.java:221)
	at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitComplete(CompletionStageUtil.java:805)
	at oracle.jdbc.internal.CompletionStageUtil$IteratorSubscription.emitItems(CompletionStageUtil.java:752)
	... 6 common frames omitted
Caused by: java.sql.SQLWarning: Warning: execution completed with warning
	at oracle.jdbc.driver.DatabaseError.addSqlWarning(DatabaseError.java:1078)
Caused by: java.sql.SQLWarning: Warning: execution completed with warning

	at oracle.jdbc.driver.DatabaseError.addSqlWarning(DatabaseError.java:1122)
	at oracle.jdbc.driver.DatabaseError.addSqlWarning(DatabaseError.java:1135)
	at oracle.jdbc.driver.T4CPreparedStatement.handleOALL8Failure(T4CPreparedStatement.java:478)
	at oracle.jdbc.driver.T4CPreparedStatement.lambda$doOall8Async$0(T4CPreparedStatement.java:221)
	at oracle.jdbc.driver.T4C8Oall.lambda$doOALLAsync$0(T4C8Oall.java:583)
	at oracle.jdbc.driver.T4CTTIfun.lambda$receiveRPCAsync$1(T4CTTIfun.java:474)
	at oracle.jdbc.driver.RestrictedLock.lambda$runUnrestricted$0(RestrictedLock.java:428)
	at oracle.jdbc.driver.RestrictedLock.callUnrestricted(RestrictedLock.java:447)
	at oracle.jdbc.driver.RestrictedLock.runUnrestricted(RestrictedLock.java:427)
	at oracle.jdbc.driver.PhysicalConnection.lambda$initializeAsyncExecutor$4(PhysicalConnection.java:1270)
	... 6 common frames omitted
2022-09-09 15:17:17,034 INFO  [reactor-http-nio-3] com.gic.eagle.txnHld.handler.VWODAHoldingEglHandler: trying to get 

Originally raised on spring-projects/spring-data-r2dbc#784

[Michael-A-McMahon]

Thanks @rathoreamrsingh, this issue identifies a problem with Oracle R2DBC: We don't have a way to filter warnings.

To correct this, I'll need to declare a new interface:
oracle.r2dbc.OracleR2dbcWarning

With this interface, user code we be able to filter warnings like this:

result.filter(segment -> !(segment instanceof OracleR2dbcWarning))

I'll hope to find time for making this fix soon.

[Michael-A-McMahon]

As a temporary workaround, you might checking for a SQLWarning as a cause:

result.filter(segment -> 
  !(segment instance of Result.Message)
  || !(((Result.Message)segment).exception().getCause() instanceof SQLWarning))

This is ugly though. I'll still want to make the proposed fix.

[rathoreamrsingh]

Hi @Michael-A-McMahon
Thanks a lot for the update.
Please update it soon
Thanks

[rathoreamrsingh]

Hi @Michael-A-McMahon,
I tried the workaround provided by you but it doesn't seems to be working, I am getting the same result.
Is it possible for you to put whole class code?
Just wanted to check I am importing and using the correct classes.
Thanks

[Michael-A-McMahon]

Hi @rathoreamrsingh, looks like I typed some code that didn't compile ("instance of").

Below is a full class that does compile (note the TODOs):

import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.Result;
import reactor.core.publisher.Flux;

import java.sql.SQLWarning;
import java.time.Duration;
import java.time.LocalDateTime;

public class Warning {
  public static void main(String[] args) {
    Flux.usingWhen(
      ConnectionFactories.get(
        // TODO: Change URL for your database
        "r2dbc:oracle://host:port/service-name")
        .create(),
      connection ->
        Flux.from(connection.createStatement(
          // TODO: Change <View_Name> to your view name
          "SELECT * from <View_Name> WHERE DATA_DT >= :P0_startDate AND DATA_DT <=:P1_endDate")
            .bind(0, LocalDateTime.now().minus(Duration.ofDays(2)))
            .bind(1, LocalDateTime.now().minus(Duration.ofDays(1)))
          .execute())
          .flatMap(result ->
            result.filter(segment -> !isWarning(segment))
              .getRowsUpdated()),
      Connection::close)
      .toStream()
      .forEach(System.out::println);
  }

  /**
   * @return {@code true} if a result Segment is a warning, or {@code false} if
   * not
   */
  private static boolean isWarning(Result.Segment segment) {
    if (!(segment instanceof Result.Message))
      return false;

    Result.Message message = (Result.Message) segment;
    return message.exception().getCause() instanceof SQLWarning;
  }


}

[rathoreamrsingh]

Thanks a lot for the update.

[Michael-A-McMahon]

I have some code written to add the OracleR2dbcWarning class, and it seems to be working well.

I've just noticed that this issue was originally spotted in #82, but I failed to recognize it at the time.

So multiple programmers are having an issue with this: That's is a good indication that Oracle R2DBC is doing something wrong :(

I'm thinking the right thing to do is to make warnings an "opt-in" feature, rather than "opt-out". Looking at JDBC, warnings are "opt-in": They are not thrown from methods that execute SQL. Instead, you may "opt-in" by calling a getWarnings method.

I think we'd all be happier if Oracle R2DBC were to follow the same model. You may opt-in to receiving warnings by calling Result.flatMap(Function<Segment, T>). Your function may then consume Message segments and report any warnings it finds.
But if you don't call faltMap, then you are opting out of receiving warnings. Oracle R2DBC would not emit them as exceptions with onError. Instead, it will just silently drop the warning.

Any thoughts about this? I hope this will be an improvement, but please let me know if you have other ideas.

[rathoreamrsingh]

This seems to be a good solution to me.
There will be some consistency between JDBC and R2DBC for this feature.