{"record":{"id":"ac6cc52448ae7ab8","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-ac6cc5","errorCode":null,"errorMessage":"Can't emulate offset clause in subquery","messagePattern":"Can't emulate offset clause in subquery","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLServerLegacySqlAstTranslator.java","lineNumber":385,"sourceCode":"\t\t}\n\t\telse if ( getClauseStack().getCurrent() == Clause.OVER ) {\n\t\t\tif ( addWhitespace ) {\n\t\t\t\tappendSql( ' ' );\n\t\t\t}\n\t\t\trenderEmptyOrderBy();\n\t\t}\n\t}\n\n\tprotected void renderEmptyOrderBy() {\n\t\t// Always need an order by clause: https://blog.jooq.org/2014/05/13/sql-server-trick-circumvent-missing-order-by-clause/\n\t\tappendSql( \"order by (select 0)\" );\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\tif ( !isRowNumberingCurrentQueryPart() ) {\n\t\t\tif ( getDialect().getVersion().isBefore( 9 ) && !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {\n\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate offset clause in subquery\" );\n\t\t\t}\n\t\t\tfinal OffsetFetchClauseMode offsetFetchClauseMode = getOffsetFetchClauseMode( queryPart );\n\t\t\tif ( offsetFetchClauseMode == OffsetFetchClauseMode.STANDARD ) {\n\t\t\t\tif ( !queryPart.hasSortSpecifications() ) {\n\t\t\t\t\tappendSql( ' ' );\n\t\t\t\t\trenderEmptyOrderBy();\n\t\t\t\t}\n\t\t\t\tfinal Expression offsetExpression;\n\t\t\t\tfinal Expression fetchExpression;\n\t\t\t\tfinal FetchClauseType fetchClauseType;\n\t\t\t\tif ( queryPart.isRoot() && hasLimit() ) {\n\t\t\t\t\tprepareLimitOffsetParameters();\n\t\t\t\t\toffsetExpression = getOffsetParameter();\n\t\t\t\t\tfetchExpression = getLimitParameter();\n\t\t\t\t\tfetchClauseType = FetchClauseType.ROWS_ONLY;\n\t\t\t\t}\n\t\t\t\telse {\n\t\t\t\t\toffsetExpression = queryPart.getOffsetClauseExpression();","sourceCodeStart":367,"sourceCodeEnd":403,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLServerLegacySqlAstTranslator.java#L367-L403","documentation":"SQLServerLegacySqlAstTranslator.visitOffsetFetchClause() throws IllegalArgumentException when a non-root query part (subquery/CTE) carries an offset clause and the dialect version is below 9 (SQL Server 2000-era). Those versions lack the features needed to emulate OFFSET inside a subquery, so translation aborts with 'Can't emulate offset clause in subquery'.","triggerScenarios":"HQL with limit/offset (setFirstResult or explicit offset syntax) inside a subquery or CTE, executed with SQLServerLegacyDialect version < 9 (or an explicitly constructed old version).","commonSituations":"Very old SQL Server 2000 databases still in production; dialect instantiated with a pinned legacy version; version misdetection through old jTDS drivers.","solutions":["Move the offset to the root query and keep subqueries unpaged","Rewrite the paginated subquery using TOP/order-by or row_number() techniques valid on that version","Upgrade SQL Server (2005+ allows row_number emulation; 2012+ has native offset/fetch)","Correct the configured dialect version if the real server is newer"],"exampleFix":"-- before\nselect o from Order o\nwhere o.id in (select s.id from Src s order by s.rank offset 10 rows fetch next 5 rows only)\n\n-- after\nselect o from Order o where o.id in :pageIds -- paginate outside the subquery","handlingStrategy":"try-catch","validationCode":"Dialect d = sessionFactory.getJdbcServices().getDialect();\nboolean oldSqlServer = d instanceof SQLServerLegacyDialect s && s.getVersion().isBefore( 9 );\nif ( oldSqlServer && queryHasSubqueryOffset( hql ) ) {\n    // rewrite: keep offsets on the root query only\n}","typeGuard":null,"tryCatchPattern":"try {\n    return query.list();\n}\ncatch ( IllegalArgumentException e ) {\n    if ( e.getMessage() != null && e.getMessage().contains( \"offset clause in subquery\" ) ) {\n        // SQL Server < 9: restructure to a row_number()-based rewrite or outer pagination\n        throw e;\n    }\n    throw e;\n}","preventionTips":["Paginate only the outer query when targeting legacy SQL Server versions","Verify the configured dialect version matches the actual server","Prefer row_number()-based patterns for subquery limits on SQL Server 2000/2005"],"tags":["sqlserver","pagination","subquery","offset-fetch","legacy-versions"],"backgroundTag":"offset-in-subquery-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}