hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset fetch clause in subquery

Error message

Can't emulate offset fetch clause in subquery

What it means

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.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseASELegacySqlAstTranslator.java:285

	@Override
	protected void visitValuesList(List<Values> valuesList) {
		visitValuesListEmulateSelectUnion( valuesList );
	}

	@Override
	public void visitValuesTableReference(ValuesTableReference tableReference) {
		append( '(' );
		visitValuesListEmulateSelectUnion( tableReference.getValuesList() );
		append( ')' );
		renderDerivedTableReferenceIdentificationVariable( tableReference );
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		assertRowsOnlyFetchClauseType( queryPart );
		if ( !queryPart.isRoot() && queryPart.hasOffsetOrFetchClause() ) {
			if ( queryPart.getFetchClauseExpression() != null && !supportsTopClause() || queryPart.getOffsetClauseExpression() != null ) {
				throw new IllegalArgumentException( "Can't emulate offset fetch clause in subquery" );
			}
		}
	}

	@Override
	protected void renderFetchExpression(Expression fetchExpression) {
		if ( supportsParameterOffsetFetchExpression() ) {
			super.renderFetchExpression( fetchExpression );
		}
		else {
			renderExpressionAsLiteral( fetchExpression, getJdbcParameterBindings() );
		}
	}

	@Override
	protected void renderOffsetExpression(Expression offsetExpression) {
		if ( supportsParameterOffsetFetchExpression() ) {
			super.renderOffsetExpression( offsetExpression );

View on GitHub (pinned to fad1729dce)

Solutions

1. Move pagination to the outer query and use an inequality predicate for the subquery (seek style: 'where e.ts > (select max(...) ...)')
2. Rewrite the subquery so it needs no OFFSET: filter with an IN list produced in Java, or use TOP-style logic the dialect supports
3. If the failure comes from a FETCH-only subquery, raise the configured dialect version (supportsTopClause) so TOP-based emulation applies
4. Compute the offset window in application code and pass concrete keys into the subquery

Example fix

// before - offset inside subquery (throws)
select e from Event e
where e.ts >= (select s.ts from Stamp s order by s.ts offset 10 rows fetch first 1 rows only)

// after - paginate the outer query only
select e from Event e
where e.ts >= :threshold
order by e.ts
offset 10 rows fetch first 1 rows only

void assertNoPaginationInSubquery(String hql) {
    Matcher m = Pattern.compile('\\(\\s*select\\b(?:(?!\\)).)*?offset\\b', Pattern.CASE_INSENSITIVE | Pattern.DOTALL).matcher(hql);
    if (m.find()) {
        throw new IllegalArgumentException(
            'Sybase ASE cannot emulate OFFSET inside a subquery; paginate the outer query');
    }
}

try {
    return em.createQuery(hql).setFirstResult(offset).setMaxResults(limit).getResultList();
}
catch (IllegalArgumentException e) {
    if (String.valueOf(e.getMessage()).contains('offset fetch clause in subquery')) {
        // fall back to seek/keyset pagination without subquery offsets
        return findByKeyset(lastSeenKey, limit);
    }
    throw e;
}

When it happens

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

Common situations: 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.

Related errors

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

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