hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate search clause for descending search specificat

Error message

Can't emulate search clause for descending search specifications

What it means

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.

Source

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

			);
			if ( currentCteStatement.getSearchClauseKind() == CteSearchClauseKind.BREADTH_FIRST ) {
				final String depthColumnName = determineDepthColumnName( currentCteStatement );
				final ColumnReference depthColumnReference = new ColumnReference(
						recursiveTableReference,
						depthColumnName,
						false,
						null,
						integerType
				);
				visitColumnReference( depthColumnReference );
				appendSql( "+1" );
				appendSql( COMMA_SEPARATOR );
				appendSql( "row(" );
				visitColumnReference( depthColumnReference );

				for ( SearchClauseSpecification searchBySpecification : currentCteStatement.getSearchBySpecifications() ) {
					if ( searchBySpecification.getSortOrder() == SortDirection.DESCENDING ) {
						throw new IllegalArgumentException( "Can't emulate search clause for descending search specifications" );
					}
					if ( searchBySpecification.getNullPrecedence() != Nulls.NONE ) {
						throw new IllegalArgumentException( "Can't emulate search clause for search specifications with explicit null precedence" );
					}
					final int selectionIndex = currentCteStatement.getCteTable()
							.getCteColumns()
							.indexOf( searchBySpecification.getCteColumn() );
					final SqlSelection sqlSelection = selectClause.getSqlSelections().get( selectionIndex );
					appendSql( COMMA_SEPARATOR );
					sqlSelection.accept( this );
				}
				appendSql( ')' );
			}
			else {
				visitColumnReference(
						new ColumnReference(
								recursiveTableReference,
								currentCteStatement.getSearchColumn().getColumnExpression(),

View on GitHub (pinned to fad1729dce)

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.

Example fix

-- before
with recursive t(id) as (...) search breadth first by sort_key desc set ord select * from t order by ord
-- after: search ascending, reverse at consumption
with recursive t(id) as (...) search breadth first by sort_key set ord select * from t order by ord desc

Defensive patterns

Strategy: validation

Validate before calling

// Before building/running the query: search specifications must be ascending when SEARCH is emulated
boolean emulated = !dialect.supportsRecursiveSearchClause();
if ( emulated && searchSpecifications.stream().anyMatch(s -> s.getSortOrder() == SortDirection.DESCENDING) ) {
    throw new IllegalArgumentException("Descending SEARCH BY not supported on this dialect; order the outer query instead");
}

Try / catch

try {
    return session.createQuery(hql, ResultDto.class).getResultList();
} catch (IllegalArgumentException e) {
    if ( "Can't emulate search clause for descending search specifications".equals(e.getMessage()) ) {
        // strip DESC from the SEARCH BY list and re-run, then order the final select descending
        return session.createQuery(hqlWithoutDesc + " order by ord desc", ResultDto.class).getResultList();
    }
    throw e;
}

Prevention

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.

When it happens

Trigger: 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.

Common situations: 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.

Related errors

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