hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate search clause for search specifications with e

Error message

Can't emulate search clause for search specifications with explicit null precedence

What it means

In emulateSearchClauseOrderWithRowAndArray (recursive UNION part, BREADTH FIRST branch), Hibernate builds a row(depth+1, ...) ordering value from the search-by columns. The emulation encodes nulls in one fixed way, so it cannot honor an explicit Nulls.FIRST or Nulls.LAST: any SearchClauseSpecification whose getNullPrecedence() != Nulls.NONE throws IllegalArgumentException during SQL rendering.

Source

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

				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(),
								false,
								null,
								currentCteStatement.getSearchColumn().getJdbcMapping()

View on GitHub (pinned to fad1729dce)

Solutions

Drop 'nulls first/last' from the search-by list — the emulation applies its own deterministic null encoding.
Make the search key non-null: coalesce the column in the CTE select (e.g. coalesce(col, '')) so null precedence is irrelevant.
Apply nulls-first/last in the final SELECT's ORDER BY instead of inside the SEARCH clause.
Use native SQL or a dialect with native SEARCH support when explicit null precedence inside the search is a hard requirement.

Example fix

-- before
with recursive t(id, name) as (...) search breadth first by name nulls first set ord select * from t
-- after: coalesce inside the CTE, plain ascending search
with recursive t(id, sort_name) as (select id, coalesce(name, '') ...) search breadth first by sort_name set ord select * from t

Defensive patterns

Strategy: validation

Validate before calling

boolean emulated = !dialect.supportsRecursiveSearchClause();
if ( emulated && searchSpecifications.stream().anyMatch(s -> s.getNullPrecedence() != Nulls.NONE) ) {
    throw new IllegalArgumentException("Explicit null precedence in SEARCH BY not emulatable on this dialect");
}

Try / catch

try {
    return session.createQuery(hql, ResultDto.class).getResultList();
} catch (IllegalArgumentException e) {
    if ( "Can't emulate search clause for search specifications with explicit null precedence".equals(e.getMessage()) ) {
        // remove NULLS FIRST/LAST from SEARCH BY, or coalesce the key, then retry
        return session.createQuery(sanitizeSearchClause(hql), ResultDto.class).getResultList();
    }
    throw e;
}

Prevention

Never put NULLS FIRST/LAST inside a SEARCH clause; apply null ordering in the outer ORDER BY.
Make search keys non-null by coalescing them in the CTE select list.
Document per-dialect SEARCH support so query authors know when emulation constraints apply.

When it happens

Trigger: HQL 'search breadth first by col nulls first set seq' (or nulls last) on a recursive CTE, or the equivalent criteria search specification with explicit null precedence, on a dialect lacking dialect.supportsRecursiveSearchClause() where the row/array emulation runs.

Common situations: Optional tree columns (nullable sort keys like nickname, secondary rank) where business logic wants nulls sorted first; porting queries from dialects where 'nulls first' is native; defaulting ORDER BY habits carried into SEARCH clauses.

Related errors

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