# Recursive CTE possible in Subselects or Joins?

**URL:** <https://discourse.hibernate.org/t/recursive-cte-possible-in-subselects-or-joins/8658>\
**Category:** Hibernate ORM\
**Created:** [December 6, 2023, 3:55pm UTC](https://discourse.hibernate.org/t/recursive-cte-possible-in-subselects-or-joins/8658 "2023-12-06T15:55:20Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![hibernategeist](https://avatars.discourse-cdn.com/v4/letter/h/d6d6ee/32.png) [@hibernategeist](https://discourse.hibernate.org/u/hibernategeist)\
**Post date:** [December 6, 2023, 3:55pm UTC](https://discourse.hibernate.org/t/recursive-cte-possible-in-subselects-or-joins/8658/1 "2023-12-06T15:55:20Z")

</div>

Hi,

is it possbile to use recursive CTEs in Subselects or Joins? For example:

```auto
final TypedQuery<String> testQuery = entityManager.createQuery("""
				SELECT c.name FROM Container c 
				WHERE c.location.id IN 
				(WITH allLocations AS (SELECT l.name name, l.id id FROM Location l WHERE l.id = :startId
				UNION ALL
				SELECT l2.name name, l2.id id FROM allLocations al JOIN Location l2 ON al.id = l2.parent.id )
				SELECT al.id FROM allLocations al)""", String.class);

testQuery.setParameter("startId", location.getId());

final List<String> containerNames = testQuery.getResultList();

```

The pure CTE delivers the expected result. But in Subselect I get the following error:

```auto
Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [SELECT c.name FROM Container c
WHERE c.location.id IN
(WITH allLocations AS (SELECT l.name name, l.id id FROM Location l WHERE l.id = :startId
UNION ALL
SELECT l2.name name, l2.id id FROM allLocations al JOIN Location l2 ON al.id = l2.parent.id )
SELECT al.id FROM allLocations al)]; this may indicate a semantic (user query) problem or a bug in the parser [SELECT c.name FROM Container c
WHERE c.location.id IN
(WITH allLocations AS (SELECT l.name name, l.id id FROM Location l WHERE l.id = :startId
UNION ALL
SELECT l2.name name, l2.id id FROM allLocations al JOIN Location l2 ON al.id = l2.parent.id )
SELECT al.id FROM allLocations al)]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:97) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	... 128 more
Caused by: java.lang.ClassCastException: class org.hibernate.query.sqm.tree.select.SqmSelectStatement cannot be cast to class org.hibernate.query.sqm.tree.select.SqmSubQuery (org.hibernate.query.sqm.tree.select.SqmSelectStatement and org.hibernate.query.sqm.tree.select.SqmSubQuery are in unnamed module of loader org.apache.catalina.loader.ParallelWebappClassLoader @32a25711)
	at org.hibernate.query.sqm.tree.cte.SqmCteStatement.<init>(SqmCteStatement.java:92) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.withInternal(AbstractSqmSelectQuery.java:177) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.withRecursiveUnionAll(AbstractSqmSelectQuery.java:127) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCte(SemanticQueryBuilder.java:761) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWithClause(SemanticQueryBuilder.java:663) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$WithClauseContext.accept(HqlParser.java:1122) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:928) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1711) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSubquery(SemanticQueryBuilder.java:5075) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:2695) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitInPredicate(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$InPredicateContext.accept(HqlParser.java:6086) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:2297) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5910) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1161) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:937) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1818) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:931) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:253) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1711) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:418) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:377) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:295) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:81) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	... 128 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:** [December 6, 2023, 4:53pm UTC](https://discourse.hibernate.org/t/recursive-cte-possible-in-subselects-or-joins/8658/2 "2023-12-06T16:53:29Z")

</div>

Hey, that looks like a bug to me.  
Please try to create a reproducer with our test case template ([hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java)) and if you are able to reproduce the issue, create a bug ticket in our issue tracker([https://hibernate.atlassian.net](https://hibernate.atlassian.net)) and attach that reproducer.

---

<div class="post-metadata">

**Author:** ![hibernategeist](https://avatars.discourse-cdn.com/v4/letter/h/d6d6ee/32.png) [@hibernategeist](https://discourse.hibernate.org/u/hibernategeist)\
**Post date:** [December 8, 2023, 2:47pm UTC](https://discourse.hibernate.org/t/recursive-cte-possible-in-subselects-or-joins/8658/3 "2023-12-08T14:47:47Z")

</div>

I have tested with plain SQL in SQL SERVER and it is also not possible to use CTE in SUBSELECT. Then it is not an bug in hibernate.

---

<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:** [December 8, 2023, 3:13pm UTC](https://discourse.hibernate.org/t/recursive-cte-possible-in-subselects-or-joins/8658/4 "2023-12-08T15:13:48Z")

</div>

Please create the reproducer and the bug report. Hibernate ORM should actually hoist the CTE into the top level in this case.