hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

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'.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SQLServerLegacySqlAstTranslator.java:385

		}
		else if ( getClauseStack().getCurrent() == Clause.OVER ) {
			if ( addWhitespace ) {
				appendSql( ' ' );
			}
			renderEmptyOrderBy();
		}
	}

	protected void renderEmptyOrderBy() {
		// Always need an order by clause: https://blog.jooq.org/2014/05/13/sql-server-trick-circumvent-missing-order-by-clause/
		appendSql( "order by (select 0)" );
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		if ( !isRowNumberingCurrentQueryPart() ) {
			if ( getDialect().getVersion().isBefore( 9 ) && !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {
				throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
			}
			final OffsetFetchClauseMode offsetFetchClauseMode = getOffsetFetchClauseMode( queryPart );
			if ( offsetFetchClauseMode == OffsetFetchClauseMode.STANDARD ) {
				if ( !queryPart.hasSortSpecifications() ) {
					appendSql( ' ' );
					renderEmptyOrderBy();
				}
				final Expression offsetExpression;
				final Expression fetchExpression;
				final FetchClauseType fetchClauseType;
				if ( queryPart.isRoot() && hasLimit() ) {
					prepareLimitOffsetParameters();
					offsetExpression = getOffsetParameter();
					fetchExpression = getLimitParameter();
					fetchClauseType = FetchClauseType.ROWS_ONLY;
				}
				else {
					offsetExpression = queryPart.getOffsetClauseExpression();

View on GitHub (pinned to fad1729dce)

Solutions

1. Move the offset to the root query and keep subqueries unpaged
2. Rewrite the paginated subquery using TOP/order-by or row_number() techniques valid on that version
3. Upgrade SQL Server (2005+ allows row_number emulation; 2012+ has native offset/fetch)
4. Correct the configured dialect version if the real server is newer

Example fix

-- before
select o from Order o
where o.id in (select s.id from Src s order by s.rank offset 10 rows fetch next 5 rows only)

-- after
select o from Order o where o.id in :pageIds -- paginate outside the subquery

Dialect d = sessionFactory.getJdbcServices().getDialect();
boolean oldSqlServer = d instanceof SQLServerLegacyDialect s && s.getVersion().isBefore( 9 );
if ( oldSqlServer && queryHasSubqueryOffset( hql ) ) {
    // rewrite: keep offsets on the root query only
}

try {
    return query.list();
}
catch ( IllegalArgumentException e ) {
    if ( e.getMessage() != null && e.getMessage().contains( "offset clause in subquery" ) ) {
        // SQL Server < 9: restructure to a row_number()-based rewrite or outer pagination
        throw e;
    }
    throw e;
}

When it happens

Trigger: 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).

Common situations: Very old SQL Server 2000 databases still in production; dialect instantiated with a pinned legacy version; version misdetection through old jTDS drivers.

Related errors

• Can't emulate offset clause in subquery
• Can't emulate offset fetch clause in subquery
• Can't emulate offset fetch clause in subquery
• Can't render offset and fetch clause for subquery
• Can't render offset and fetch clause for subquery

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/ac6cc52448ae7ab8. Report an issue: GitHub.