hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

RDMSOS2200SqlAstTranslator.visitOffsetFetchClause() only renders limit/offset for the root query part. On the Unisys RDMS for OS2200 dialect there is no way to emulate OFFSET inside a subquery/CTE/derived query part, so any non-root query part carrying an offset clause throws IllegalArgumentException during SQL translation.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/RDMSOS2200SqlAstTranslator.java:55

	protected LockStrategy determineLockingStrategy(
			QuerySpec querySpec,
			Locking.FollowOn followOnLocking) {
		return LockStrategy.NONE;
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		if ( queryPart.isRoot() ) {
			if ( hasLimit() ) {
				prepareLimitOffsetParameters();
				renderFetch( getLimitParameter(), getOffsetParameter(), FetchClauseType.ROWS_ONLY );
			}
			else if ( queryPart.getFetchClauseExpression() != null ) {
				renderFetch( queryPart.getFetchClauseExpression(), queryPart.getOffsetClauseExpression(), queryPart.getFetchClauseType() );
			}
		}
		else if ( queryPart.getOffsetClauseExpression() != null ) {
			throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
		}
	}

	@Override
	protected void renderFetchPlusOffsetExpression(
			Expression fetchClauseExpression,
			Expression offsetClauseExpression,
			int offset) {
		renderFetchPlusOffsetExpressionAsSingleParameter( fetchClauseExpression, offsetClauseExpression, offset );
	}

	@Override
	protected boolean needsRowsToSkip() {
		return true;
	}

	@Override
	protected void renderComparison(Expression lhs, ComparisonOperator operator, Expression rhs) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Move pagination to the outer (root) query only
2. Rewrite the paginated subquery using row_number() over (...) filtering or a join
3. Precompute the limited key set in Java and bind it as a parameter list

Example fix

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

-- after
select o from Order o where o.id in :pageIds

boolean hasSubqueryOffset(String hql) {
    return hql != null && hql.toLowerCase( Locale.ROOT ).matches(
        "(?s).*\\b(offset|skip)\\b.*" ) && hql.toLowerCase( Locale.ROOT ).contains( " in (select" );
}
// on RDMSOS2200: reject or rewrite such queries before execution

try {
    return query.list();
}
catch ( IllegalArgumentException e ) {
    if ( e.getMessage() != null && e.getMessage().contains( "offset clause in subquery" ) ) {
        // restructure: paginate the outer query or precompute the id page
        throw e;
    }
    throw e;
}

When it happens

Trigger: HQL/Criteria where a subquery, CTE or derived table contains limit/offset — e.g. 'where id in (select id order by x offset 10 rows fetch next 5 rows only)' — executed on RDMSOS2200.

Common situations: Portable query libraries that embed paginated subqueries; migrating an application onto a legacy Unisys OS/2200 database.

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/aab7cfb1f49a3c13. Report an issue: GitHub.