{"record":{"id":"1a8f536763798679","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-search-clause-for-descending-search","errorCode":null,"errorMessage":"Can't emulate search clause for descending search specifications","messagePattern":"Can't emulate search clause for descending search specifications","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":2719,"sourceCode":"\t\t\t);\n\t\t\tif ( currentCteStatement.getSearchClauseKind() == CteSearchClauseKind.BREADTH_FIRST ) {\n\t\t\t\tfinal String depthColumnName = determineDepthColumnName( currentCteStatement );\n\t\t\t\tfinal ColumnReference depthColumnReference = new ColumnReference(\n\t\t\t\t\t\trecursiveTableReference,\n\t\t\t\t\t\tdepthColumnName,\n\t\t\t\t\t\tfalse,\n\t\t\t\t\t\tnull,\n\t\t\t\t\t\tintegerType\n\t\t\t\t);\n\t\t\t\tvisitColumnReference( depthColumnReference );\n\t\t\t\tappendSql( \"+1\" );\n\t\t\t\tappendSql( COMMA_SEPARATOR );\n\t\t\t\tappendSql( \"row(\" );\n\t\t\t\tvisitColumnReference( depthColumnReference );\n\n\t\t\t\tfor ( SearchClauseSpecification searchBySpecification : currentCteStatement.getSearchBySpecifications() ) {\n\t\t\t\t\tif ( searchBySpecification.getSortOrder() == SortDirection.DESCENDING ) {\n\t\t\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate search clause for descending search specifications\" );\n\t\t\t\t\t}\n\t\t\t\t\tif ( searchBySpecification.getNullPrecedence() != Nulls.NONE ) {\n\t\t\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate search clause for search specifications with explicit null precedence\" );\n\t\t\t\t\t}\n\t\t\t\t\tfinal int selectionIndex = currentCteStatement.getCteTable()\n\t\t\t\t\t\t\t.getCteColumns()\n\t\t\t\t\t\t\t.indexOf( searchBySpecification.getCteColumn() );\n\t\t\t\t\tfinal SqlSelection sqlSelection = selectClause.getSqlSelections().get( selectionIndex );\n\t\t\t\t\tappendSql( COMMA_SEPARATOR );\n\t\t\t\t\tsqlSelection.accept( this );\n\t\t\t\t}\n\t\t\t\tappendSql( ')' );\n\t\t\t}\n\t\t\telse {\n\t\t\t\tvisitColumnReference(\n\t\t\t\t\t\tnew ColumnReference(\n\t\t\t\t\t\t\t\trecursiveTableReference,\n\t\t\t\t\t\t\t\tcurrentCteStatement.getSearchColumn().getColumnExpression(),","sourceCodeStart":2701,"sourceCodeEnd":2737,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L2701-L2737","documentation":"While rendering the recursive UNION part of a recursive CTE, renderRecursiveCteVirtualSelections delegates to emulateSearchClauseOrderWithRowAndArray when the dialect lacks native SEARCH support but supports the row/array emulation (supportsRecursiveClauseArrayAndRowEmulation()). For BREADTH FIRST it builds row(depth+1, search-cols...); that synthetic ordering only preserves ASCENDING order, so a search specification with SortDirection.DESCENDING throws IllegalArgumentException mid-render.","triggerScenarios":"HQL 'search breadth first by col desc set seq' on a recursive CTE, or criteria JpaCteCriteria.setSearchClauseKind(BREADTH_FIRST, ...) with a descending sort specification, on a dialect where dialect.supportsRecursiveSearchClause() is false and the row/array emulation is chosen.","commonSituations":"Porting SQL Server / Oracle recursive queries that order siblings descending (newest child first); wanting reverse chronological tree walks; using tree APIs (e.g. adjacency list explosions) that expect DESC ordering by a sort key.","solutions":["Remove DESC from the search-by specification — order the final SELECT (not the search clause) by seq desc or by depth desc to get reverse output order.","Invert the sort key in the CTE itself (e.g. search by a negated or complementary ascending expression) when descending traversal semantics are required.","Use a database/dialect with native SEARCH clause support, or run the query as native SQL where descending search is supported.","If you control the mapping, order by an inverted numeric or reversed string key projected alongside the rows."],"exampleFix":"-- before\nwith recursive t(id) as (...) search breadth first by sort_key desc set ord select * from t order by ord\n-- after: search ascending, reverse at consumption\nwith recursive t(id) as (...) search breadth first by sort_key set ord select * from t order by ord desc","handlingStrategy":"validation","validationCode":"// Before building/running the query: search specifications must be ascending when SEARCH is emulated\nboolean emulated = !dialect.supportsRecursiveSearchClause();\nif ( emulated && searchSpecifications.stream().anyMatch(s -> s.getSortOrder() == SortDirection.DESCENDING) ) {\n    throw new IllegalArgumentException(\"Descending SEARCH BY not supported on this dialect; order the outer query instead\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return session.createQuery(hql, ResultDto.class).getResultList();\n} catch (IllegalArgumentException e) {\n    if ( \"Can't emulate search clause for descending search specifications\".equals(e.getMessage()) ) {\n        // strip DESC from the SEARCH BY list and re-run, then order the final select descending\n        return session.createQuery(hqlWithoutDesc + \" order by ord desc\", ResultDto.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["Standardize on ascending SEARCH BY specifications; do sibling ordering in the consuming SELECT.","Encode reverse traversal as an inverted key column inside the CTE (search ascending by it).","Add a lint rule or code review checklist: no DESC/NULLS FIRST/NULLS LAST inside SEARCH clauses of shared HQL."],"tags":["hibernate","recursive-cte","search-clause","sort-order"],"backgroundTag":"recursive-cte-search-clause","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}