I’m migrating software, and during this process, I encountered some troubles with our queries.
SELECT Count(*)
FROM my_table a
WHERE a.id = COALESCE(:id, a.id)
org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [SELECT Count(*) FROM my_table a WHERE a.id = COALESCE(?, a.id)] [The type for parameter ‘@p0’ cannot be deduced in this context.]
I would like to know why this worked in Hibernate 5 and doesn’t work anymore in Hibernate 6, because, in my point of view, this case doesn’t show any sort of bad coding. I would also like to know what I could do to solve this problem.
The project was running on hibernate 5.3.20 before I upgraded to 6.4.4
The site does not let me post the full stack, so here is the hibernate part of the stack stace
Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [SELECT Count(*) FROM my_table a WHERE a.id = COALESCE(?, a.id)] [O tipo de parâmetro '@P0' não pode ser deduzido neste contexto.] [n/a]
at org.hibernate@6.4.4.Final//org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
at org.hibernate@6.4.4.Final//org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)
at org.hibernate@6.4.4.Final//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108)
at org.hibernate@6.4.4.Final//org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:94)
at org.hibernate@6.4.4.Final//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:265)
at org.hibernate@6.4.4.Final//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:167)
at org.hibernate@6.4.4.Final//org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getMetaData(AbstractResultSetAccess.java:36)
at org.hibernate@6.4.4.Final//org.hibernate.sql.results.jdbc.internal.AbstractResultSetAccess.getColumnCount(AbstractResultSetAccess.java:52)
at org.hibernate@6.4.4.Final//org.hibernate.query.results.ResultSetMappingImpl.resolve(ResultSetMappingImpl.java:193)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.resolveJdbcValuesSource(JdbcSelectExecutorStandardImpl.java:325)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:115)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:83)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:76)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:65)
at org.hibernate@6.4.4.Final//org.hibernate.query.sql.internal.NativeSelectQueryPlanImpl.performList(NativeSelectQueryPlanImpl.java:138)
at org.hibernate@6.4.4.Final//org.hibernate.query.sql.internal.NativeQueryImpl.doList(NativeQueryImpl.java:621)
at org.hibernate@6.4.4.Final//org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
at org.hibernate@6.4.4.Final//org.hibernate.query.spi.AbstractSelectionQuery.getSingleResult(AbstractSelectionQuery.java:564)
at deployment.TEST-PROJ.war//com.core.sdk.dao.GenericDAO.buscaNumRegistrosPorParametrosRuntimeNativeQuery(GenericDAO.java:325)
at deployment.TEST-PROJ.war//com.core.su.dao.OrganogramaSuDAO.buscaNumRegistrosPorParametros(OrganogramaSuDAO.java:178)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.jboss.as.ee@33.0.2.Final//org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:35)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:509)
at org.jboss.as.weld.common@33.0.2.Final//org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.delegateInterception(Jsr299BindingsInterceptor.java:62)
at org.jboss.as.weld.common@33.0.2.Final//org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:72)
at org.jboss.as.weld.common@33.0.2.Final//org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:85)
at org.jboss.as.ee@33.0.2.Final//org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:46)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.as.ejb3@33.0.2.Final//org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:26)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.as.jpa@33.0.2.Final//org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:30)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.as.ee@33.0.2.Final//org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:28)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:40)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:53)
at org.jboss.as.ee@33.0.2.Final//org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:35)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.as.ejb3@33.0.2.Final//org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:34)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.as.ejb3@33.0.2.Final//org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:39)
at org.jboss.invocation@2.0.1.Final//org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
at org.jboss.as.ejb3@33.0.2.Final//org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:184)
... 468 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: O tipo de parâmetro '@P0' não pode ser deduzido neste contexto.
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:270)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1735)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:675)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:594)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7745)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4391)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:276)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQueryInternal(SQLServerPreparedStatement.java:524)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(SQLServerParameterMetaData.java:354)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:3354)
at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:3365)
at org.jboss.ironjacamar.jdbcadapters@3.0.9.Final//org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.getParameterMetaData(WrappedPreparedStatement.java:1249)
at org.hibernate@6.4.4.Final//org.hibernate.type.descriptor.jdbc.ObjectNullResolvingJdbcType$1.doBindNull(ObjectNullResolvingJdbcType.java:45)
at org.hibernate@6.4.4.Final//org.hibernate.type.descriptor.jdbc.BasicBinder.bind(BasicBinder.java:51)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.internal.AbstractJdbcParameter.bindParameterValue(AbstractJdbcParameter.java:130)
at org.hibernate@6.4.4.Final//org.hibernate.sql.exec.internal.AbstractJdbcParameter.bindParameterValue(AbstractJdbcParameter.java:101)
at org.hibernate@6.4.4.Final//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.bindParameters(DeferredResultSetAccess.java:203)
at org.hibernate@6.4.4.Final//org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:233)
... 511 more
I doubt that this same query worked before with the same database and same driver version. The error clearly comes from SQL Server.
I’m no expert on SQL Server, but if casting works, I would go with that solution.
You are right, it isn’t the original query, but this query is a sample that gives the same error in both versions of Hibernate, I have already tested it.
I’m not upgrading anything except the software, so the database and driver stayed on the same version.
Unfortunately, casting is not a solution I would like to choose because I have more than 4000 queries that use COALESCE. Its true that the error comes from SQL Server, but why does not the error show up in 5.3.20?
Hibernate ORM does not alter native queries in any form other than potentially expanding multi-valued parameters, so I doubt that this query ever worked.
At the end of the days, the same SQL will end up being passed to the driver and finally to the server. If you only changed the Hibernate ORM version, but still use the same driver and database version as well as the same SQL query, you will have to show me proof that it worked in ORM 5.3.
public class testes {
private EntityManagerFactory entityManagerFactory;
@Before
public void init() {
entityManagerFactory = Persistence.createEntityManagerFactory("MYPU");
}
@After
public void destroy() {
entityManagerFactory.close();
}
@Test
public void hhh123Test() throws Exception {
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
Query qry = em.createNativeQuery("SELECT Count(*) FROM my_table a WHERE a.id = COALESCE(:id, a.id)");
qry.setParameter("id", null);
Object res = qry.getSingleResult();
System.out.println("res: " + res.toString());
em.getTransaction().commit();
em.close();
}
}
Hibernate 6.4.4:
Hibernate: SELECT Count(*) FROM my_table a WHERE a.id = COALESCE(?, a.id)
16:24:37.156 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - O tipo de parâmetro '@P0' não pode ser deduzido neste contexto.
The same query in 5.3 but other result:
Hibernate: SELECT Count(*) FROM my_table a WHERE a.id = COALESCE(?, a.id)
res: 7
In the two tests i was using the same version of the sql driver but for different java versions.
If you’re binding a null value, Hibernate ORM asks the driver to determine the type of the parameter to understand how to bind the null value.
I don’t know how this worked in Hibernate ORM 5, but I bet it just guessed a type and if you’re unlucky, that type might not work for your query and/or fail when passing a non-null value.
We can certainly try to improve the situation, but my guess is that the driver/server simply is uncapable to determine the correct parameter type for parameters passed to a function like coalesce, that doesn’t have a fixed type.
Create an improvement request in our issue tracker and attach that reproducer. In the meantime, unless you want to work on this yourself, you will have to cast the parameter explicitly.
I found that the class NativeQuery has a setParameter method that allows specifying the type of the parameter. in my case, I believe using the type String solves this issue.
NativeQuery<?> qry = em.unwrap(Session.class).createNativeQuery(
"SELECT Count(*) FROM my_table a WHERE a.id = COALESCE(:id, a.id)",
Object.class
);
qry.setParameter("id", null, String.class);
I would like to point out that I dont think this is the best solution for this issue.