# Hibernate 6.6.x function table

**URL:** <https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164>\
**Category:** Hibernate ORM\
**Created:** [March 6, 2025, 11:59am UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164 "2025-03-06T11:59:47Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Oscar\_Fanchin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/oscar_fanchin/32/3883_2.png) [@Oscar\_Fanchin](https://discourse.hibernate.org/u/Oscar_Fanchin)\
**Post date:** [March 6, 2025, 11:59am UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/1 "2025-03-06T11:59:47Z")

</div>

Hello, is possible register a function like

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);

?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [March 6, 2025, 8:23pm UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/2 "2025-03-06T20:23:34Z")

</div>

Hibernate ORM 7.0 will provide support for set-returning functions. Also see [Hibernate ORM User Guide](https://docs.jboss.org/hibernate/orm/7.0/userguide/html_single/Hibernate_User_Guide.html#hql-from-set-returning-functions)

---

<div class="post-metadata">

**Author:** ![Oscar\_Fanchin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/oscar_fanchin/32/3883_2.png) [@Oscar\_Fanchin](https://discourse.hibernate.org/u/Oscar_Fanchin)\
**Post date:** [March 7, 2025, 8:07am UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/3 "2025-03-07T08:07:56Z")

</div>

thanks Beikov, when the final version is available, I will definitely try it!

---

<div class="post-metadata">

**Author:** ![Oscar\_Fanchin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/oscar_fanchin/32/3883_2.png) [@Oscar\_Fanchin](https://discourse.hibernate.org/u/Oscar_Fanchin)\
**Post date:** [May 3, 2025, 6:49pm UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/4 "2025-05-03T18:49:07Z")

</div>

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

```auto
	  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

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [May 20, 2025, 4:20pm UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/5 "2025-05-20T16:20:17Z")

</div>

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:

```sql
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.

---

<div class="post-metadata">

**Author:** ![Oscar\_Fanchin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/oscar_fanchin/32/3883_2.png) [@Oscar\_Fanchin](https://discourse.hibernate.org/u/Oscar_Fanchin)\
**Post date:** [May 25, 2025, 5:36pm UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/6 "2025-05-25T17:36:20Z")

</div>

```auto
select k.id, x.nameFunction
from Department k 
join lateral getDataFunction(:date, k.id) x

```

Great, this syntax works perfectly, I’ll try to open an issue for improvement anyway.

---

<div class="post-metadata">

**Author:** ![Oscar\_Fanchin](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/oscar_fanchin/32/3883_2.png) [@Oscar\_Fanchin](https://discourse.hibernate.org/u/Oscar_Fanchin)\
**Post date:** [May 28, 2025, 5:41am UTC](https://discourse.hibernate.org/t/hibernate-6-6-x-function-table/11164/7 "2025-05-28T05:41:12Z")

</div>

Hello, i have opened this issue

> **[Jira](https://hibernate.atlassian.net/browse/HHH-19488)**