{"record":{"id":"859d3b2f08f2bafc","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-fetch-clause-in-subquery","errorCode":null,"errorMessage":"Can't emulate offset fetch clause in subquery","messagePattern":"Can't emulate offset fetch clause in subquery","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseASELegacySqlAstTranslator.java","lineNumber":285,"sourceCode":"\t@Override\n\tprotected void visitValuesList(List<Values> valuesList) {\n\t\tvisitValuesListEmulateSelectUnion( valuesList );\n\t}\n\n\t@Override\n\tpublic void visitValuesTableReference(ValuesTableReference tableReference) {\n\t\tappend( '(' );\n\t\tvisitValuesListEmulateSelectUnion( tableReference.getValuesList() );\n\t\tappend( ')' );\n\t\trenderDerivedTableReferenceIdentificationVariable( tableReference );\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\tassertRowsOnlyFetchClauseType( queryPart );\n\t\tif ( !queryPart.isRoot() && queryPart.hasOffsetOrFetchClause() ) {\n\t\t\tif ( queryPart.getFetchClauseExpression() != null && !supportsTopClause() || queryPart.getOffsetClauseExpression() != null ) {\n\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate offset fetch clause in subquery\" );\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderFetchExpression(Expression fetchExpression) {\n\t\tif ( supportsParameterOffsetFetchExpression() ) {\n\t\t\tsuper.renderFetchExpression( fetchExpression );\n\t\t}\n\t\telse {\n\t\t\trenderExpressionAsLiteral( fetchExpression, getJdbcParameterBindings() );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderOffsetExpression(Expression offsetExpression) {\n\t\tif ( supportsParameterOffsetFetchExpression() ) {\n\t\t\tsuper.renderOffsetExpression( offsetExpression );","sourceCodeStart":267,"sourceCodeEnd":303,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseASELegacySqlAstTranslator.java#L267-L303","documentation":"SybaseASELegacySqlAstTranslator.visitOffsetFetchClause() throws IllegalArgumentException ('Can't emulate offset fetch clause in subquery') when a non-root QueryPart (a subquery) carries pagination that the dialect cannot emulate. Sybase ASE legacy only supports TOP N in subqueries, and only when the dialect version supports TOP at all: any OFFSET inside a subquery always throws, and a FETCH-only subquery throws when supportsTopClause() is false. Root-level pagination is handled by the emulated fetch/offset machinery and is unaffected.","triggerScenarios":"An HQL query containing a subquery with ORDER BY ... OFFSET ... FETCH FIRST, e.g. keyset/seek emulation like 'where (e.ts, e.id) > (select s.ts, s.id from S s order by ... offset :o rows fetch first :n rows only)'; criteria subqueries with setFirstResult/setMaxResults on Sybase ASE legacy.","commonSituations":"Pagination strategies that push OFFSET/FETCH into subqueries (seek pagination, 'select the Nth group' patterns); dialect minimum version configured below the release that supports TOP, making even FETCH-only subqueries fail; queries ported from databases that allow LIMIT in subqueries.","solutions":["Move pagination to the outer query and use an inequality predicate for the subquery (seek style: 'where e.ts > (select max(...) ...)')","Rewrite the subquery so it needs no OFFSET: filter with an IN list produced in Java, or use TOP-style logic the dialect supports","If the failure comes from a FETCH-only subquery, raise the configured dialect version (supportsTopClause) so TOP-based emulation applies","Compute the offset window in application code and pass concrete keys into the subquery"],"exampleFix":"// before - offset inside subquery (throws)\nselect e from Event e\nwhere e.ts >= (select s.ts from Stamp s order by s.ts offset 10 rows fetch first 1 rows only)\n\n// after - paginate the outer query only\nselect e from Event e\nwhere e.ts >= :threshold\norder by e.ts\noffset 10 rows fetch first 1 rows only","handlingStrategy":"try-catch","validationCode":"void assertNoPaginationInSubquery(String hql) {\n    Matcher m = Pattern.compile('\\\\(\\\\s*select\\\\b(?:(?!\\\\)).)*?offset\\\\b', Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(hql);\n    if (m.find()) {\n        throw new IllegalArgumentException(\n            'Sybase ASE cannot emulate OFFSET inside a subquery; paginate the outer query');\n    }\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).setFirstResult(offset).setMaxResults(limit).getResultList();\n}\ncatch (IllegalArgumentException e) {\n    if (String.valueOf(e.getMessage()).contains('offset fetch clause in subquery')) {\n        // fall back to seek/keyset pagination without subquery offsets\n        return findByKeyset(lastSeenKey, limit);\n    }\n    throw e;\n}","preventionTips":["Keep OFFSET/FETCH only at the root query level for Sybase ASE","Prefer keyset (seek) predicates over offset subqueries for deep pagination","Verify the configured dialect version supports TOP if FETCH-only subqueries are required"],"tags":["hibernate","sybase-ase","sql-translator","pagination","subquery","offset-fetch"],"backgroundTag":"subquery-pagination-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}