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

Cannot fetch a NULL literal from a Result #17

Closed
lukaseder opened this issue Mar 29, 2021 · 8 comments
Closed

Cannot fetch a NULL literal from a Result #17

lukaseder opened this issue Mar 29, 2021 · 8 comments

Comments

@lukaseder
Copy link

I'm trying to fetch the result of a NULL literal without explicit type from an R2DBC result, but I can't seem to do it:

System.out.println((
    Flux.from(connectionFactory.create())
        .flatMap(c -> c
            .createStatement("select null from dual")
            .execute())
        .flatMap(it -> it.map((r, m) -> r.get(0, Integer.class)))
        .collectList()
        .block().get(0)
));

This produces:

Exception in thread "main" java.lang.NullPointerException: Row mapping function returned null
	at oracle.jdbc.driver.InsensitiveScrollableResultSet$RowPublisher.mapCurrentRow(InsensitiveScrollableResultSet.java:1302)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
	at oracle.jdbc.driver.PhysicalConnection.lambda$createUserCodeExecutor$10(PhysicalConnection.java:11713)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at oracle.jdbc.driver.PhysicalConnection.lambda$createUserCodeExecutor$11(PhysicalConnection.java:11711)
	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)
	Suppressed: java.lang.Exception: #block terminated with an error
		at reactor.core.publisher.BlockingSingleSubscriber.blockingGet(BlockingSingleSubscriber.java:99)
		at reactor.core.publisher.Mono.block(Mono.java:1703)

When I try fetching binary data:

System.out.println((
    Flux.from(connectionFactory.create())
        .flatMap(c -> c
            .createStatement("select null from dual")
            .execute())
        .flatMap(it -> it.map((r, m) -> r.get(0, ByteBuffer.class)))
        .collectList()
        .block().get(0)
));

I'm getting:

Exception in thread "main" java.lang.IllegalArgumentException: java.sql.SQLException: Ungültiger Spaltentyp
	at oracle.r2dbc.impl.OracleReactiveJdbcAdapter$OracleJdbcRow.getObject(OracleReactiveJdbcAdapter.java:1193)
	at oracle.r2dbc.impl.OracleRowImpl.getByteBuffer(OracleRowImpl.java:272)
	at oracle.r2dbc.impl.OracleRowImpl.convertColumnValue(OracleRowImpl.java:245)
	at oracle.r2dbc.impl.OracleRowImpl.get(OracleRowImpl.java:137)
	at org.jooq.testscripts.R2DBC.lambda$2(R2DBC.java:30)
	at oracle.r2dbc.impl.OracleResultImpl$2.lambda$publishRows$0(OracleResultImpl.java:119)
	at oracle.r2dbc.impl.OracleReactiveJdbcAdapter.lambda$publishRows$10(OracleReactiveJdbcAdapter.java:698)
	at oracle.jdbc.driver.InsensitiveScrollableResultSet$RowPublisher.mapCurrentRow(InsensitiveScrollableResultSet.java:1299)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700)
	at oracle.jdbc.driver.PhysicalConnection.lambda$createUserCodeExecutor$10(PhysicalConnection.java:11713)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at oracle.jdbc.driver.PhysicalConnection.lambda$createUserCodeExecutor$11(PhysicalConnection.java:11711)
	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)
	Suppressed: java.lang.Exception: #block terminated with an error
		at reactor.core.publisher.BlockingSingleSubscriber.blockingGet(BlockingSingleSubscriber.java:99)
		at reactor.core.publisher.Mono.block(Mono.java:1703)
		at org.jooq.testscripts.R2DBC.main(R2DBC.java:32)
Caused by: java.sql.SQLException: Ungültiger Spaltentyp
	at oracle.jdbc.driver.Redirector$99.redirect(Redirector.java:1448)
	at oracle.jdbc.driver.Redirector$99.redirect(Redirector.java:1444)
	at oracle.jdbc.driver.Representation.getObject(Representation.java:567)
	at oracle.jdbc.driver.Accessor.getObject(Accessor.java:1025)
	at oracle.jdbc.driver.OracleStatement.getObject(OracleStatement.java:6827)
	at oracle.jdbc.driver.InsensitiveScrollableResultSet$RowPublisher$ExpiringRow.getObject(InsensitiveScrollableResultSet.java:1390)
	at oracle.r2dbc.impl.OracleReactiveJdbcAdapter$OracleJdbcRow.getObject(OracleReactiveJdbcAdapter.java:1185)

I'm using:

<dependency>
    <groupId>com.oracle.database.r2dbc</groupId>
    <artifactId>oracle-r2dbc</artifactId>
    <version>0.1.0</version>
</dependency>        
<dependency>
    <groupId>io.r2dbc</groupId>
    <artifactId>r2dbc-spi</artifactId>
    <version>0.9.0.M1</version>
</dependency>
@lukaseder
Copy link
Author

OK, half of the problem is my misunderstanding of reactor's flatMap(), which can't seem to handle nulls. These work:

System.out.println((
    Flux.from(connectionFactory.create())
        .flatMap(c -> c
            .createStatement("select cast(null as number) from dual")
            .execute())
        .flatMap(it -> it.map((r, m) -> new AtomicReference<>(r.get(0, Integer.class))))
        .collectList()
        .block().get(0)
));

System.out.println((
    Flux.from(connectionFactory.create())
        .flatMap(c -> c
            .createStatement("select cast(null as raw(10)) from dual")
            .execute())
        .flatMap(it -> it.map((r, m) -> new AtomicReference<>(r.get(0, ByteBuffer.class))))
        .collectList()
        .block().get(0)
));

But this continues to throw the wrong data type exception:

System.out.println((
    Flux.from(connectionFactory.create())
        .flatMap(c -> c
            .createStatement("select null from dual")
            .execute())
        .flatMap(it -> it.map((r, m) -> new AtomicReference<>(r.get(0, ByteBuffer.class))))
        .collectList()
        .block().get(0)
));

lukaseder added a commit to jOOQ/jOOQ that referenced this issue Mar 29, 2021
@lukaseder
[#11700] Work around
655c2a1 
oracle/oracle-r2dbc#17
@Michael-A-McMahon
Copy link
Member

Thanks for your findings!

Concerning the Invalid Column Type Error
It seems that the null literal is regarded as a VARCHAR type by Oracle Database, and Oracle JDBC does not support conversion from VARCHAR to byte[] (When OracleRow.get(int/String, Class) is called with ByteBuffer.class, Oracle R2DBC requests a byte[] from Oracle JDBC, and then wraps it in a ByteBuffer; The JDBC 4.3 Specification does not require drivers to support conversions to ByteBuffer).
So, casting the null literal to RAW works around that because Oracle JDBC will support conversions of RAW to byte[].
However, we can note that Oracle JDBC doesn't actually need to convert anything when the column value is the null literal. I'll check with the rest of the Oracle JDBC team and see if we can allow ResultSet/OracleRow to just return null for the null literal, rather than treat the value as if it were a VARCHAR column that might have null and non-null values.

Concerning the NullPointerException:
The reason Oracle R2DBC can't allow the mapping function return null, and the reason why Flux.flatMap won't accept a null value, is rooted in the Reactive Streams Specification. The specification does not allow null to passed to Subscriber.onNext(Object). I think AtomicReference is good way to work around that. Optional might be worth considering as well.

https://github.com/reactive-streams/reactive-streams-jvm/blob/v1.0.3/README.md#specification

Spec Item 2.13:
Calling onSubscribe, onNext, onError or onComplete MUST return normally except when any provided parameter is null in which case it MUST throw a java.lang.NullPointerException to the caller

@lukaseder
Copy link
Author

Thanks a lot for the feedback, @Michael-A-McMahon

I think AtomicReference is good way to work around that. Optional might be worth considering as well.

Yes sure. Those things were just artifacts of trying to create a minimal example, in whose attempt I got side tracked...

@Douglas-Surber
Copy link

Douglas-Surber commented Mar 30, 2021 via email

@Michael-A-McMahon
Copy link
Member

Closing this issue as the fix for mapping the null literal to byte[] can only be made in Oracle JDBC, not in Oracle R2DBC.

@lukaseder lukaseder mentioned this issue Jun 30, 2021
Closed
3 tasks
@lukaseder
Copy link
Author

Closing this issue as the fix for mapping the null literal to byte[] can only be made in Oracle JDBC, not in Oracle R2DBC.

Is there a workaround of some way? I'm finding it hard to predictably read NULL values of type BLOB. Another example:

create table t (b blob);
insert into t values (null);

And then:

Class<?> klass = ...?

System.out.println(
Flux.from(cf.create())
    .flatMap(c -> c.createStatement("select b from t").execute())
    .flatMap(it -> it.map((r, m) -> Optional.ofNullable(r.get(0, klass))))
    .collectList()
    .block()
);

I cannot use klass = ByteBuffer.class:

Exception in thread "main" oracle.r2dbc.impl.OracleR2dbcExceptions$OracleR2dbcNonTransientException: Unsupported SQL to Java type mapping. SQL Type: BLOB, Column Index: 0, Java Type: java.nio.ByteBuffer
	at oracle.r2dbc.impl.OracleR2dbcExceptions.newNonTransientException(OracleR2dbcExceptions.java:297)
	at oracle.r2dbc.impl.OracleRowImpl.unsupportedTypeMapping(OracleRowImpl.java:455)
	at oracle.r2dbc.impl.OracleRowImpl.requireSupportedTypeMapping(OracleRowImpl.java:418)
	at oracle.r2dbc.impl.OracleRowImpl.convertColumnValue(OracleRowImpl.java:255)
	at oracle.r2dbc.impl.OracleRowImpl.get(OracleRowImpl.java:142)
	at org.jooq.testscripts.R2DBC.lambda$2(R2DBC.java:43)
	at oracle.r2dbc.impl.OracleResultImpl$2.lambda$publishRows$0(OracleResultImpl.java:139)
	at oracle.r2dbc.impl.OracleReactiveJdbcAdapter.lambda$publishRows$12(OracleReactiveJdbcAdapter.java:702)
	at oracle.jdbc.driver.InsensitiveScrollableResultSet$RowPublisher.mapCurrentRow(InsensitiveScrollableResultSet.java:1299)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
	at oracle.jdbc.driver.PhysicalConnection.lambda$createUserCodeExecutor$10(PhysicalConnection.java:11713)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
	at oracle.jdbc.driver.PhysicalConnection.lambda$createUserCodeExecutor$11(PhysicalConnection.java:11711)
	at java.base/java.util.concurrent.ForkJoinTask$RunnableExecuteAction.exec(ForkJoinTask.java:1395)
	at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
	at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1176)
	at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1647)
	at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1614)
	at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)
	Suppressed: java.lang.Exception: #block terminated with an error
		at reactor.core.publisher.BlockingSingleSubscriber.blockingGet(BlockingSingleSubscriber.java:99)
		at reactor.core.publisher.Mono.block(Mono.java:1703)
		at org.jooq.testscripts.R2DBC.main(R2DBC.java:45)

I can use klass = io.r2dbc.spi.Blob.class, but that's not such a nice type to receive for small to medium sized BLOB values

@lukaseder lukaseder mentioned this issue Jun 30, 2021
Open
@Michael-A-McMahon
Copy link
Member

@lukaseder, I've opened a new issue to discuss this here: #32
TLDR: BLOB can only be mapped to io.r2dbc.spi.Blob, and mapping to ByteBuffer is not supported.

@lukaseder
Copy link
Author

Perfect, thanks

@lukaseder lukaseder mentioned this issue May 23, 2022
Open
35 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@lukaseder @Douglas-Surber @Michael-A-McMahon