{"record":{"id":"c219022083f5bfed","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-fetch-clause-type-c21902","errorCode":null,"errorMessage":"Can't emulate fetch clause type: ","messagePattern":"Can't emulate fetch clause type: ","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":5038,"sourceCode":"\t\t}\n\t\tif ( offsetExpression != null ) {\n\t\t\tfinal Stack<Clause> clauseStack = getClauseStack();\n\t\t\tappendSql( \" offset \" );\n\t\t\tclauseStack.push( Clause.OFFSET );\n\t\t\ttry {\n\t\t\t\trenderOffsetExpression( offsetExpression );\n\t\t\t}\n\t\t\tfinally {\n\t\t\t\tclauseStack.pop();\n\t\t\t}\n\t\t}\n\t}\n\n\tprotected void assertRowsOnlyFetchClauseType(QueryPart queryPart) {\n\t\tif ( !queryPart.isRoot() || !hasLimit() ) {\n\t\t\tfinal FetchClauseType fetchClauseType = queryPart.getFetchClauseType();\n\t\t\tif ( fetchClauseType != null && fetchClauseType != FetchClauseType.ROWS_ONLY ) {\n\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate fetch clause type: \" + fetchClauseType );\n\t\t\t}\n\t\t}\n\t}\n\n\tprotected QueryPart getQueryPartForRowNumbering() {\n\t\treturn queryPartForRowNumbering;\n\t}\n\n\tprotected boolean isRowNumberingCurrentQueryPart() {\n\t\treturn queryPartForRowNumbering != null;\n\t}\n\n\tprotected void emulateFetchOffsetWithWindowFunctions(QueryPart queryPart, boolean emulateFetchClause) {\n\t\tif ( queryPart.isRoot() && hasLimit() ) {\n\t\t\tprepareLimitOffsetParameters();\n\t\t\temulateFetchOffsetWithWindowFunctions(\n\t\t\t\t\tqueryPart,\n\t\t\t\t\tgetOffsetParameter(),","sourceCodeStart":5020,"sourceCodeEnd":5056,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L5020-L5056","documentation":"When a dialect lacks native fetch-clause support, Hibernate emulates pagination by wrapping the query with row_number()/dense_rank(). assertRowsOnlyFetchClauseType checks any non-root query part (or a root without a limit context): if that query part carries a fetch clause type other than ROWS_ONLY (PERCENT_ONLY, ROWS_WITH_TIES, PERCENT_WITH_TIES), emulation is impossible and this IllegalArgumentException is thrown, because percent/ties semantics cannot be reproduced with row numbering.","triggerScenarios":"Using HQL fetch clause variants such as 'fetch first 10 percent rows only', 'fetch first 10 rows with ties', or 'limit 10 percent' inside a subquery (non-root query part) or in a context without an outer limit, on a dialect that does not natively support that fetch clause type (e.g. MySQL, H2 before 2.x, SQL Server pre-2022 for percent-with-ties).","commonSituations":"Porting HQL with WITH TIES / PERCENT from Oracle/PostgreSQL to MySQL; putting fetch-first in a subquery or CTE member; Criteria queries with FetchClauseType.JpaOnlyWithTies on unsupported databases; Hibernate 6.x where HQL limit syntax gained percent/ties options.","solutions":["Remove WITH TIES / PERCENT and use plain 'fetch first N rows only' (ROWS_ONLY)","Move the fetch clause to the outermost (root) query part where the dialect/emulation can handle it","Use a database/dialect with native support for the desired fetch clause type (e.g. SQL Server 2022, Oracle 12c+, PostgreSQL 13+ for with-ties)","Emulate with-ties manually via rank() over(order by ...) subquery in a native query"],"exampleFix":"// before (MySQL dialect)\nList<Employee> top = session.createQuery(\n    \"select e from Employee e where e.dept = :d fetch first 5 rows with ties order by e.salary desc\").list();\n\n// after\nList<Employee> top = session.createNativeQuery(\n    \"select * from (select e.*, rank() over(order by e.salary desc) rk from emp e where e.dept=:d) t where rk <= 5\").list();","handlingStrategy":"validation","validationCode":"// Reject WITH TIES / PERCENT fetch clauses before executing when the dialect lacks support\nboolean ok = dialect.supportsFetchClause(FetchClauseType.ROWS_ONLY)\n          || desiredFetchType == FetchClauseType.ROWS_ONLY;\nif (!ok) { /* downgrade to rows-only or reject the request */ }","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Keep fetch clauses to plain ROWS_ONLY inside subqueries","Gate percent/with-ties queries behind a dialect capability check in your repository layer","Test pagination flavors on the production database, not only on H2"],"tags":["hibernate","fetch-clause","pagination","dialect-emulation","with-ties"],"backgroundTag":"fetch-clause-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}