{"record":{"id":"3b9227f117a09523","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-recursive-ctes","errorCode":null,"errorMessage":"Can't emulate recursive CTEs!","messagePattern":"Can't emulate recursive CTEs!","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":2217,"sourceCode":"\t\t// If CTE inlining is needed, collect all recursive CTEs, since these can't be inlined\n\t\tif ( needsCteInlining() && !originalCteStatements.isEmpty() ) {\n\t\t\tcteStatements = new ArrayList<>( originalCteStatements.size() );\n\t\t\tfor ( CteStatement cteStatement : originalCteStatements ) {\n\t\t\t\tif ( cteStatement.isRecursive() ) {\n\t\t\t\t\tcteStatements.add( cteStatement );\n\t\t\t\t}\n\t\t\t}\n\t\t}\n\t\telse {\n\t\t\tcteStatements = originalCteStatements;\n\t\t}\n\t\tfinal Collection<CteObject> cteObjects = cteContainer.getCteObjects().values();\n\t\tif ( cteStatements.isEmpty() && cteObjects.isEmpty() ) {\n\t\t\treturn;\n\t\t}\n\t\tif ( !dialect.supportsWithClause() ) {\n\t\t\tif ( isRecursive( cteStatements ) && cteObjects.isEmpty() ) {\n\t\t\t\tthrow new UnsupportedOperationException( \"Can't emulate recursive CTEs!\" );\n\t\t\t}\n\t\t\t// This should be unreachable, because #needsCteInlining() must return true if org.hibernate.dialect.Dialect#supportsWithClause() returns false,\n\t\t\t// and hence the cteStatements should either contain a recursive CTE or be empty\n\t\t\tthrow new IllegalStateException( \"Non-recursive CTEs found that need inlining, but were collected: \" + cteStatements );\n\t\t}\n\t\tfinal boolean renderRecursiveKeyword = needsRecursiveKeywordInWithClause() && isRecursive( cteStatements );\n\t\tif ( renderRecursiveKeyword && !dialect.supportsRecursiveCTE() ) {\n\t\t\tthrow new UnsupportedOperationException( \"Can't emulate recursive CTEs!\" );\n\t\t}\n\t\t// Here we compute if the CTEs should be pushed to the top level WITH clause\n\t\tfinal boolean isTopLevel = clauseStack.isEmpty();\n\t\tfinal boolean pushToTopLevel;\n\t\tif ( isTopLevel ) {\n\t\t\tpushToTopLevel = false;\n\t\t}\n\t\telse {\n\t\t\tpushToTopLevel = !dialect.supportsNestedWithClause()\n\t\t\t\t\t|| !dialect.supportsWithClauseInSubquery() && isInSubquery();","sourceCodeStart":2199,"sourceCodeEnd":2235,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L2199-L2235","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Verify the database version is detected correctly — set hibernate.dialect explicitly (e.g., MySQLDialect for >= 8) so supportsRecursiveCTE() returns true.","Upgrade the database to a version with WITH RECURSIVE support (MySQL 8+, MariaDB 10.2+).","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).","Execute the recursive CTE as a native query, which bypasses SQM translation."],"exampleFix":"// before — recursive CTE HQL on a DB without WITH RECURSIVE\nList<TreeNode> path = session.createQuery(\n    \"with recursive path(id,parent) as (select id,parent from Node where id=:root \" +\n    \"union all select n.id,n.parent from Node n join path p on n.id=p.parent) \" +\n    \"select n from Node n where n.id in (select id from path)\", TreeNode.class)\n    .setParameter(\"root\", rootId).list();\n\n// after — iterative traversal in Java\nList<TreeNode> path = new ArrayList<>();\nLong current = rootId;\nwhile (current != null) {\n    TreeNode n = session.find(TreeNode.class, current);\n    path.add(n);\n    current = n.getParent() != null ? n.getParent().getId() : null;\n}","handlingStrategy":"validation","validationCode":"org.hibernate.dialect.Dialect d = sessionFactory.getJdbcServices().getDialect();\nboolean recursive = hql.toLowerCase().contains(\"with recursive\");\nif (recursive && (!d.supportsWithClause() || !d.supportsRecursiveCTE())) {\n    throw new UnsupportedOperationException(\n        \"Database \" + d.getClass().getSimpleName() + \" cannot run recursive CTEs; use native SQL or iterative logic\");\n}","typeGuard":null,"tryCatchPattern":"try { session.createQuery(recursiveHql, TreeNode.class).list(); }\ncatch (UnsupportedOperationException e) {\n    if (e.getMessage().equals(\"Can't emulate recursive CTEs!\")) {\n        results = traverseIteratively(rootId); // app-level BFS/DFS loop\n    } else { throw e; }\n}","preventionTips":["Feature-detect at startup: if (!dialect.supportsRecursiveCTE()) disable or reroute recursive-query features.","Confirm the dialect detects the real DB version (MySQL 8+, MariaDB 10.2+) — set hibernate.dialect explicitly when JDBC metadata lies.","Keep an iterative traversal fallback for hierarchy queries when portability matters."],"tags":["hibernate","orm","cte","recursive-query","mysql","sybase","dialect-capability"],"backgroundTag":"recursive-cte-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}