CREATE FUNCTION get_ordine_dettagli(p_id BIGINT, p_data DATE) RETURNS TABLE(id BIGINT, prodotto TEXT, quantita INT, totale NUMERIC) AS $$ BEGIN RETURN QUERY SELECT o.id, p.nome, o.quantita, (o.quantita * p.prezzo) FROM ordine o JOIN prodotto p ON o.prodotto_id = p.id WHERE o.id = p_id AND o.data_ordine = p_data; END; $$ LANGUAGE plpgsql;
and call it in hql with this sintax
`@Query(“SELECT o FROM get_ordine_dettagli(:id, :data) o”) List getDettagliOrdine(@Param(“id”) Long id, @Param(“data”) LocalDate data);
Hello, Beikov, i have tested this feature in 7.0.0.CR1, great work!
Only one case gave me a problem, I would like to understand if I am doing something wrong or if it is not foreseen at the moment.
this query
select k.id, d.nameFunciton
from Department k join lateral (
select x.nameFunction nameFunction from getDataFunction(:date, k.id) x
) d
produces the following stack trace at runtime
Caused by: java.lang.IllegalArgumentException: Could not construct set-returning function. Maybe you forgot to use ‘lateral’?
at org.hibernate.query.sqm.function.SelfRenderingSqmSetReturningFunction.convertToSqlAst(SelfRenderingSqmSetReturningFunction.java:188)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.createFunctionTableGroup(BaseSqmToSqlAstConverter.java:2768)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeFromClauseRoot(BaseSqmToSqlAstConverter.java:2691)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at org.hibernate.query.sqm.tree.from.SqmFromClause.visitRoots(SqmFromClause.java:82)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFromClause(BaseSqmToSqlAstConverter.java:2470)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.querySpec(BaseSqmToSqlAstConverter.java:1967)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1946)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:352)
at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:123)
at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:252)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1829)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:6994)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSubQueryExpression(BaseSqmToSqlAstConverter.java:352)
at org.hibernate.query.sqm.tree.select.SqmSubQuery.accept(SqmSubQuery.java:764)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeDerivedJoin(BaseSqmToSqlAstConverter.java:3444)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeExplicitJoin(BaseSqmToSqlAstConverter.java:3178)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeJoins(BaseSqmToSqlAstConverter.java:2835)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeFromClauseRoot(BaseSqmToSqlAstConverter.java:2735)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
at org.hibernate.query.sqm.tree.from.SqmFromClause.visitRoots(SqmFromClause.java:82)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFromClause(BaseSqmToSqlAstConverter.java:2470)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.querySpec(BaseSqmToSqlAstConverter.java:1967)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:1946)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:352)
at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:123)
at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:252)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1829)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1524)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:352)
at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:258)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:694)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:472)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:393)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:368)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:399)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:143)
at org.hibernate.query.Query.getResultList(Query.java:118)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:419)
at jdk.proxy2/jdk.proxy2.$Proxy146.getResultList(Unknown Source)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:130)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:93)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:159)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:147)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:170)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:149)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:69)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
… 9 more
Caused by: org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - com.example.departmenttest.entity.Department(k)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3669)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3604)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3588)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBasicValuedPath(BaseSqmToSqlAstConverter.java:4299)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBasicValuedPath(BaseSqmToSqlAstConverter.java:352)
at org.hibernate.query.sqm.tree.domain.SqmBasicValuedSimplePath.accept(SqmBasicValuedSimplePath.java:210)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(BaseSqmToSqlAstConverter.java:7157)
at org.hibernate.query.sqm.function.SelfRenderingSqmSetReturningFunction.resolveSqlAstArguments(SelfRenderingSqmSetReturningFunction.java:145)
at org.hibernate.query.sqm.function.SelfRenderingSqmSetReturningFunction.convertToSqlAst(SelfRenderingSqmSetReturningFunction.java:184)
… 65 more
Not sure if I would consider this being a bug, though the error message is unfortunate in your particular case. Essentially, correlation through lateral doesn’t work for subquery FROM clause root elements.
Try this instead:
select k.id, x.nameFunction
from Department k
join lateral getDataFunction(:date, k.id) x
Also, please create a Jira ticket with an improvement request for adding support for this. I can see the value of writing a query this way. Our checks are probably just too strict right now.