java.lang.NullPointerException: Cannot invoke "java.util.List.size()" because the return value of "io.vertx.sqlclient.RowSet.columnsNames()" is null

[codermannn]

import jakarta.persistence.EntityManagerFactory;
import org.hibernate.reactive.mutiny.Mutiny;

@Inject
EntityManagerFactory emf;

public Uni<Integer> myFunc() {
  Mutiny.SessionFactory factory = emf.unwrap(Mutiny.SessionFactory.class);
  return factory.withSession(session -> { session.createNativeQuery(storedProcedureCall).getSingleResultOrNull()
}

The call errors out when the stored procedure does not return any result with

java.lang.NullPointerException: Cannot invoke "java.util.List.size()" because the return value of "io.vertx.sqlclient.RowSet.columnsNames()" is null

[blafond]

@lohiarahul Could you provide more info for your use-case? ... Entity definition, what DB are you testing against and details on your stored procedure? Thx

[DavideD]

This is a native queries, I don't think entities matter that much.
But it would help to know which database you are using and what the SQL you are passing looks like (what's the value of storedProcedureCall?).

At the moment, we only test stored procedures for PostgreSQL:

• CustomStoredProcedureSqlTest
• CustomOneToOneStoredProcedureSqlTest

I suspect we will probably need a null-check anyway

[DavideD]

@lohiarahul, I've just noticed that you are calling .getSingleResultOrNull() but if you don't expect any return values, you should call .executeUpdate()

[DavideD]

@lohiarahul let us know if this solve your problem.
@blafond If that's the problem, can you check if we can throw a better error message, please?

[DavideD]

Maybe we should just return null...not sure what happens in Hibernate ORM

[blafond]

@DavideD I added a test to CustomStoredProcedureTest which makes this call: .createNativeQuery( SELECT_SP_SQL ).getSingleResultOrNull() and it throws the user's exception.

[DavideD]

Did you check what happens in ORM? We should try to be consistent

[blafond]

Had already started that.

[codermannn]

@blafond I am using MySQL 8.0.
@DavideD I don't believe there's a executeMethod on SelectionQuery. ref: https://hibernate.org/reactive/documentation/2.0/javadocs/org/hibernate/reactive/mutiny/Mutiny.SelectionQuery.html. Guess I need to use session.createMutationQuery() for such cases

[DavideD]

createNativeQuery [returns a Query that inherits executeUpdate from MutatationQuery.

This is what your function should look like:

public Uni<Integer> myFunc() {
	Mutiny.SessionFactory factory = getMutinySessionFactory();
	return factory.withTransaction( session -> session.createNativeQuery( storedProcedureCall ).executeUpdate() );
}

Because this is an update, I've replaced withSession with withTransaction.

[gavinking]

Whatever the case, it's still unacceptable that we throw an NPE. If this was caused by illegal usage of the API, we need a better exception.

[DavideD]

I've tested quickly with ORM and the following exception is thrown:

rg.hibernate.exception.GenericJDBCException: JDBC exception executing SQL [CREATE OR REPLACE FUNCTION PROC_INSERT_RECORD (    TEXT_PARAM IN varchar(255),    ID_PARAM IN bigint   )  RETURNS void AS $BODY$     BEGIN         insert into simplerecord (text, updated, id) values (TEXT_PARAM, localtimestamp, ID_PARAM);     END; $BODY$ LANGUAGE plpgsql;] [No results were returned by the query.] [n/a]
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:258)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:164)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36)
	at org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52)
	at org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:477)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:267)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:168)
	at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:93)
	at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31)
	at org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:97)
	at org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:623)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
	at org.hibernate.query.spi.AbstractSelectionQuery.getSingleResultOrNull(AbstractSelectionQuery.java:595)
	at org.hibernate.reactive.CustomStoredProcedureSqlTest.prepareOrmFactory(CustomStoredProcedureSqlTest.java:116)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:727)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
	at io.vertx.junit5.RunTestOnContext.lambda$null$2(RunTestOnContext.java:154)
	at io.vertx.core.impl.ContextInternal.dispatch(ContextInternal.java:277)
	at io.vertx.core.impl.ContextInternal.dispatch(ContextInternal.java:259)
	at io.vertx.core.impl.EventLoopContext.lambda$runOnContext$0(EventLoopContext.java:43)
	at io.netty.util.concurrent.AbstractEventExecutor.runTask(AbstractEventExecutor.java:173)
	at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:166)
	at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:470)
	at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:566)
	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:829)
Caused by: org.postgresql.util.PSQLException: No results were returned by the query.
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:135)
	at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:240)
	... 33 more

@gavinking This error is thrown by the Postgres JDBC driver, but we could just return null if the query doesn't return any value. Should we be consistent with Hibernate ORM or should we just make it work? I don't see any drawback in allowing it.

[DavideD]

Given the javadoc for getSingleResultOrNull:

Asynchronously execute this query, returning a single row that matches the query, or null if the query returns no results, throwing an exception if the query returns more than one matching row.

I'm prone to say that returning null is fine in this situation.