{"record":{"id":"31dfd321d2801259","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-search-clause-for-search-specificati","errorCode":null,"errorMessage":"Can't emulate search clause for search specifications with explicit null precedence","messagePattern":"Can't emulate search clause for search specifications with explicit null precedence","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":2722,"sourceCode":"\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(),\n\t\t\t\t\t\t\t\tfalse,\n\t\t\t\t\t\t\t\tnull,\n\t\t\t\t\t\t\t\tcurrentCteStatement.getSearchColumn().getJdbcMapping()","sourceCodeStart":2704,"sourceCodeEnd":2740,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L2704-L2740","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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."],"exampleFix":"-- before\nwith recursive t(id, name) as (...) search breadth first by name nulls first set ord select * from t\n-- after: coalesce inside the CTE, plain ascending search\nwith recursive t(id, sort_name) as (select id, coalesce(name, '') ...) search breadth first by sort_name set ord select * from t","handlingStrategy":"validation","validationCode":"boolean emulated = !dialect.supportsRecursiveSearchClause();\nif ( emulated && searchSpecifications.stream().anyMatch(s -> s.getNullPrecedence() != Nulls.NONE) ) {\n    throw new IllegalArgumentException(\"Explicit null precedence in SEARCH BY not emulatable on this dialect\");\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 search specifications with explicit null precedence\".equals(e.getMessage()) ) {\n        // remove NULLS FIRST/LAST from SEARCH BY, or coalesce the key, then retry\n        return session.createQuery(sanitizeSearchClause(hql), ResultDto.class).getResultList();\n    }\n    throw e;\n}","preventionTips":["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."],"tags":["hibernate","recursive-cte","search-clause","null-precedence"],"backgroundTag":"recursive-cte-search-clause","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}