{"record":{"id":"125d03d8a7c6f3de","repo":"hibernate/hibernate-orm","slug":"could-not-determine-a-depth-column-name-after-5-tr","errorCode":null,"errorMessage":"Could not determine a depth column name after 5 tries!","messagePattern":"Could not determine a depth column name after 5 tries!","errorType":"exception","errorClass":"IllegalStateException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":2450,"sourceCode":"\t\t\tfinal String name = tries == 0 ? baseName : (baseName + \"_\" + tries);\n\t\t\tfor ( CteColumn cteColumn : cte.getCteTable().getCteColumns() ) {\n\t\t\t\tif ( name.equals( cteColumn.getColumnExpression() ) ) {\n\t\t\t\t\tcontinue OUTER;\n\t\t\t\t}\n\t\t\t}\n\t\t\tif ( cte.getSearchColumn() != null && name.equals( cte.getSearchColumn().getColumnExpression() ) ) {\n\t\t\t\tcontinue;\n\t\t\t}\n\t\t\tif ( cte.getCycleMarkColumn() != null && name.equals( cte.getCycleMarkColumn().getColumnExpression() ) ) {\n\t\t\t\tcontinue;\n\t\t\t}\n\t\t\tif ( cte.getCyclePathColumn() != null && name.equals( cte.getCyclePathColumn().getColumnExpression() ) ) {\n\t\t\t\tcontinue;\n\t\t\t}\n\n\t\t\treturn name;\n\t\t}\n\t\tthrow new IllegalStateException( \"Could not determine a depth column name after 5 tries!\" );\n\t}\n\n\tprotected String determineCyclePathColumnName(CteStatement cte) {\n\t\tfinal CteColumn cyclePathColumn = cte.getCyclePathColumn();\n\t\tif ( cyclePathColumn != null ) {\n\t\t\treturn cyclePathColumn.getColumnExpression();\n\t\t}\n\t\tString baseName = \"path\";\n\t\tOUTER: for ( int tries = 0; tries < 5; tries++ ) {\n\t\t\tfinal String name = tries == 0 ? baseName : (baseName + \"_\" + tries);\n\t\t\tfor ( CteColumn cteColumn : cte.getCteTable().getCteColumns() ) {\n\t\t\t\tif ( name.equals( cteColumn.getColumnExpression() ) ) {\n\t\t\t\t\tcontinue OUTER;\n\t\t\t\t}\n\t\t\t}\n\t\t\tif ( cte.getSearchColumn() != null\n\t\t\t\t\t&& name.equals( cte.getSearchColumn().getColumnExpression() ) ) {\n\t\t\t\tcontinue;","sourceCodeStart":2432,"sourceCodeEnd":2468,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L2432-L2468","documentation":"When emulating a BREADTH FIRST search clause (dialect without native SEARCH support), determineDepthColumnName must invent a column name for the emulated depth counter. It tries 'depth', 'depth_1' .. 'depth_4', skipping any name already taken by a CTE column, the search column, the cycle mark column, or the cycle path column. If all five candidates collide it throws IllegalStateException — an artificial-name exhaustion caused by the CTE's own column names.","triggerScenarios":"A recursive CTE with a SEARCH BREADTH FIRST clause (HQL 'search breadth first by ... set seq' or criteria setSearchClauseKind(BREADTH_FIRST, ...)) whose CTE table already exposes columns named exactly depth, depth_1, depth_2, depth_3 and depth_4 (or those names collide with the search/cycle columns), on a dialect where dialect.supportsRecursiveSearchClause() is false.","commonSituations":"Domain models with hierarchical data that already carry a chain of depth-tracking columns (depth, depth_1, ... from a migration or denormalization); refactoring an existing recursive CTE that used those names as regular data columns; search/cycle column explicitly named 'depth_N' colliding with the emulated counter.","solutions":["Rename the CTE's data columns so they do not occupy all of depth, depth_1..depth_4 (any single free candidate is enough).","Give the search column an explicit, non-colliding name via HQL '... set <search_col>' or the criteria API so fewer emulated names are needed.","Drop the SEARCH clause and compute ordering in the consuming query (order by an explicit depth column you project yourself).","Fall back to session.createNativeQuery(...) with hand-written SQL if the column names cannot change."],"exampleFix":"-- before: HQL CTE declaring columns that exhaust the emulated depth names\nwith recursive t(id, depth, depth_1, depth_2, depth_3, depth_4) as (...) search breadth first by id set ord select * from t\n-- after: free up at least one candidate name\nwith recursive t(id, lvl, lvl_1, lvl_2, lvl_3, lvl_4) as (...) search breadth first by id set ord select * from t","handlingStrategy":"validation","validationCode":"// Before executing, ensure the emulated depth names are not all taken\njava.util.Set<String> reserved = java.util.Set.of(\"depth\", \"depth_1\", \"depth_2\", \"depth_3\", \"depth_4\");\nboolean collision = cteColumnNames.containsAll(reserved); // cteColumnNames = names you project in the CTE\nif ( collision && !dialect.supportsRecursiveSearchClause() ) {\n    throw new IllegalStateException(\"Rename CTE columns: emulated depth column name cannot be determined\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(treeHql, ResultDto.class).getResultList();\n} catch (IllegalStateException e) {\n    if ( e.getMessage() != null && e.getMessage().contains(\"depth column name\") ) {\n        throw new IllegalStateException(\"CTE column names depth..depth_4 collide with the emulated search depth counter; rename them\", e);\n    }\n    throw e;\n}","preventionTips":["Treat 'depth' and 'depth_1'..'depth_4' as reserved names in recursive CTEs that use SEARCH BREADTH FIRST on emulating dialects.","Prefer explicit, business-prefixed column names (lvl, node_depth) over generic 'depth' in CTE select lists.","Cover SEARCH-clause CTEs with tests running against the same dialect as production (H2 results hide emulation paths production may take)."],"tags":["hibernate","recursive-cte","search-clause","name-collision"],"backgroundTag":"column-name-collision","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}