hibernate/hibernate-orm · error · UnsupportedOperationException

Can't emulate recursive CTEs!

Error message

Can't emulate recursive CTEs!

What it means

visitCteContainer throws UnsupportedOperationException("Can't emulate recursive CTEs!") in two cases: the dialect has no WITH/CTE support at all (dialect.supportsWithClause() false, e.g., Sybase ASE) and the statement needs a recursive CTE; or the dialect supports WITH and the translator must render the RECURSIVE keyword but dialect.supportsRecursiveCTE() is false (e.g., MySQL < 8.0.x / MariaDB below the CTE-capable version). Hibernate can inline plain CTEs but cannot inline recursion.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:2217

		// If CTE inlining is needed, collect all recursive CTEs, since these can't be inlined
		if ( needsCteInlining() && !originalCteStatements.isEmpty() ) {
			cteStatements = new ArrayList<>( originalCteStatements.size() );
			for ( CteStatement cteStatement : originalCteStatements ) {
				if ( cteStatement.isRecursive() ) {
					cteStatements.add( cteStatement );
				}
			}
		}
		else {
			cteStatements = originalCteStatements;
		}
		final Collection<CteObject> cteObjects = cteContainer.getCteObjects().values();
		if ( cteStatements.isEmpty() && cteObjects.isEmpty() ) {
			return;
		}
		if ( !dialect.supportsWithClause() ) {
			if ( isRecursive( cteStatements ) && cteObjects.isEmpty() ) {
				throw new UnsupportedOperationException( "Can't emulate recursive CTEs!" );
			}
			// This should be unreachable, because #needsCteInlining() must return true if org.hibernate.dialect.Dialect#supportsWithClause() returns false,
			// and hence the cteStatements should either contain a recursive CTE or be empty
			throw new IllegalStateException( "Non-recursive CTEs found that need inlining, but were collected: " + cteStatements );
		}
		final boolean renderRecursiveKeyword = needsRecursiveKeywordInWithClause() && isRecursive( cteStatements );
		if ( renderRecursiveKeyword && !dialect.supportsRecursiveCTE() ) {
			throw new UnsupportedOperationException( "Can't emulate recursive CTEs!" );
		}
		// Here we compute if the CTEs should be pushed to the top level WITH clause
		final boolean isTopLevel = clauseStack.isEmpty();
		final boolean pushToTopLevel;
		if ( isTopLevel ) {
			pushToTopLevel = false;
		}
		else {
			pushToTopLevel = !dialect.supportsNestedWithClause()
					|| !dialect.supportsWithClauseInSubquery() && isInSubquery();

View on GitHub (pinned to fad1729dce)

Solutions

1. Verify the database version is detected correctly — set hibernate.dialect explicitly (e.g., MySQLDialect for >= 8) so supportsRecursiveCTE() returns true.
2. Upgrade the database to a version with WITH RECURSIVE support (MySQL 8+, MariaDB 10.2+).
3. Rewrite the traversal without a recursive CTE: repeated self-joins with fixed depth, or an iterative application-level loop (select children per level until no rows).
4. Execute the recursive CTE as a native query, which bypasses SQM translation.

Example fix

// before — recursive CTE HQL on a DB without WITH RECURSIVE
List<TreeNode> path = session.createQuery(
    "with recursive path(id,parent) as (select id,parent from Node where id=:root " +
    "union all select n.id,n.parent from Node n join path p on n.id=p.parent) " +
    "select n from Node n where n.id in (select id from path)", TreeNode.class)
    .setParameter("root", rootId).list();

// after — iterative traversal in Java
List<TreeNode> path = new ArrayList<>();
Long current = rootId;
while (current != null) {
    TreeNode n = session.find(TreeNode.class, current);
    path.add(n);
    current = n.getParent() != null ? n.getParent().getId() : null;
}

org.hibernate.dialect.Dialect d = sessionFactory.getJdbcServices().getDialect();
boolean recursive = hql.toLowerCase().contains("with recursive");
if (recursive && (!d.supportsWithClause() || !d.supportsRecursiveCTE())) {
    throw new UnsupportedOperationException(
        "Database " + d.getClass().getSimpleName() + " cannot run recursive CTEs; use native SQL or iterative logic");
}

try { session.createQuery(recursiveHql, TreeNode.class).list(); }
catch (UnsupportedOperationException e) {
    if (e.getMessage().equals("Can't emulate recursive CTEs!")) {
        results = traverseIteratively(rootId); // app-level BFS/DFS loop
    } else { throw e; }
}

When it happens

Trigger: An HQL query using 'with recursive ...' (recursive CTE feature) or producing a recursive CTE internally, executed on a database whose Dialect reports no CTE support or no recursive-CTE support — old MySQL/MariaDB, Sybase ASE, or a dialect version misdetected below its CTE-capable release.

Common situations: Running recursive HQL (tree/hierarchy traversal) on MySQL 5.7 or MariaDB 10.1; deploying to Sybase ASE; database version not detected correctly (missing hibernate.dialect or JDBC metadata hiding the real version) so a capable DB is treated as older.

Related errors

• Illegal search order attribute '{}' passed, which is not par
• Null is an illegal value for cycle mark values!
• Illegal cycle attribute '{}' passed, which is not part of th
• Dialect does not support values lists for insert statements
• Insert conflict clause with constraint column names is not s

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/3b9227f117a09523. Report an issue: GitHub.