{"record":{"id":"fed0edeb6c24b749","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-fetch-clause-type","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-community-dialects/src/main/java/org/hibernate/community/dialect/H2LegacySqlAstTranslator.java","lineNumber":243,"sourceCode":"\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\tif ( isRowsOnlyFetchClauseType( queryPart ) ) {\n\t\t\tif ( supportsOffsetFetchClause() ) {\n\t\t\t\trenderOffsetFetchClause( queryPart, true );\n\t\t\t}\n\t\t\telse {\n\t\t\t\trenderLimitOffsetClause( queryPart );\n\t\t\t}\n\t\t}\n\t\telse {\n\t\t\tif ( supportsOffsetFetchClausePercentWithTies() ) {\n\t\t\t\trenderOffsetFetchClause( queryPart, true );\n\t\t\t}\n\t\t\telse {\n\t\t\t\t// FETCH PERCENT and WITH TIES were introduced along with window functions\n\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate fetch clause type: \" + queryPart.getFetchClauseType() );\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderSelectTupleComparison(\n\t\t\tList<SqlSelection> lhsExpressions,\n\t\t\tSqlTuple tuple,\n\t\t\tComparisonOperator operator) {\n\t\temulateSelectTupleComparison( lhsExpressions, tuple.getExpressions(), operator, true );\n\t}\n\n\t@Override\n\tpublic void visitInSubQueryPredicate(InSubQueryPredicate inSubQueryPredicate) {\n\t\tfinal SqlTuple lhsTuple;\n\t\t// As of 1.4.200 this is supported\n\t\tif ( getDialect().getVersion().isBefore( 1, 4, 200 )\n\t\t\t\t&& ( lhsTuple = SqlTupleContainer.getSqlTuple( inSubQueryPredicate.getTestExpression() ) ) != null","sourceCodeStart":225,"sourceCodeEnd":261,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/H2LegacySqlAstTranslator.java#L225-L261","documentation":"H2LegacySqlAstTranslator.visitOffsetFetchClause distinguishes rows-only paging from PERCENT / WITH TIES fetch clauses. PERCENT and WITH TIES arrived in H2 1.4.198 together with window functions; on older H2 versions (supportsOffsetFetchClausePercentWithTies() is false) there is no way to emulate them, so the translator throws IllegalArgumentException naming the FetchClauseType it cannot render.","triggerScenarios":"HQL 'fetch first 20 percent rows only', 'fetch next 10 rows with ties', or 'limit 10 percent with ties' (or the Criteria/HQL API producing FetchClauseType.PERCENT_ONLY / PERCENT_WITH_TIES / ROWS_WITH_TIES) executed against H2 older than 1.4.198 under the legacy dialect. Plain rows-only paging falls back to LIMIT/OFFSET and never throws.","commonSituations":"Legacy applications pinned to old embedded H2 (1.4.195-1.4.197 or earlier) that adopt new pagination HQL; test data queries using with-ties for deterministic ordering; upgrading Hibernate without upgrading the H2 jar in legacy distributions.","solutions":["Upgrade the H2 dependency to 1.4.198+ (ideally 2.x with the non-legacy dialect) so PERCENT/WITH TIES render natively","Drop percent/with-ties: use plain 'fetch first N rows only' / setMaxResults(N), which renders as LIMIT on any H2 1.4.x","Emulate WITH TIES manually with a native H2 query using a subquery on the order-by rank (or window function if H2 >= 1.4.198), or page the rows-only superset and expand ties in Java","For PERCENT, compute the absolute row count first (count query) and apply a rows-only fetch of the computed N"],"exampleFix":"// before (HQL, throws on H2 < 1.4.198 legacy dialect)\nselect e from Employee e order by e.salary desc fetch first 10 percent rows with ties\n\n// after (rows-only fetch, works on all H2 1.4.x)\nselect e from Employee e order by e.salary desc fetch first 10 rows only","handlingStrategy":"validation","validationCode":"// H2 legacy: PERCENT/WITH TIES need >= 1.4.198, rows-only fallback needs >= 1.4.195\nDialect d = sessionFactory.getJdbcServices().getDialect();\nboolean percentWithTiesOk = !(d instanceof org.hibernate.community.dialect.H2LegacyDialect)\n    || d.getVersion().isSameOrAfter(1, 4, 198);\nif (!percentWithTiesOk && usesPercentOrWithTies(hql)) {\n    hql = downgradeToRowsOnly(hql); // 'fetch first N rows only'\n}","typeGuard":"static boolean fetchClauseSafe(Dialect d, FetchClauseType type) {\n    if (type == FetchClauseType.ROWS_ONLY) return true;\n    return !(d instanceof org.hibernate.community.dialect.H2LegacyDialect) || d.getVersion().isSameOrAfter(1, 4, 198);\n}","tryCatchPattern":"try {\n    rows = session.createQuery(hql).list(); // 'fetch first 10 percent rows with ties'\n} catch (IllegalArgumentException e) {\n    if (e.getMessage().startsWith(\"Can't emulate fetch clause type\")) {\n        // retry with rows-only fetch or computed absolute count\n    } else throw e;\n}","preventionTips":["Pin the H2 test dependency to a current version instead of relying on the legacy dialect fallbacks","Treat PERCENT/WITH TIES as database-specific and feature-detect before use","For percent paging, compute the row count first and page by absolute rows"],"tags":["hibernate","h2","legacy-dialect","pagination","fetch-percent","with-ties","limit-offset"],"backgroundTag":"fetch-clause-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}