hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

SybaseSqlAstTranslator.visitOffsetFetchClause throws IllegalArgumentException('Can't emulate offset clause in subquery') whenever a NON-ROOT query part carries an offset clause -- even without a fetch -- because the SQL Anywhere rendering cannot emulate OFFSET inside a subquery. The check runs only outside full-join emulation query parts and after assertRowsOnlyFetchClauseType.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SybaseSqlAstTranslator.java:224

	@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) {
		if ( !currentFullJoinEmulationHelper().isFullJoinEmulationQueryPart( queryPart ) ) {
			assertRowsOnlyFetchClauseType( queryPart );
			if ( !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {
				throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
			}
		}
	}

	@Override
	public void visitQuerySpec(QuerySpec querySpec) {
		final var helper = currentFullJoinEmulationHelper();
		final boolean needsNestedHelper =
				helper.hasActiveFullJoinEmulation()
						&& !helper.isFullJoinEmulationQueryPart( querySpec );
		if ( needsNestedHelper ) {
			fullJoinEmulations.push( new FullJoinEmulation( this ) );
		}
		try {
			final var currentHelper = currentFullJoinEmulationHelper();
			if ( !currentHelper.renderFullJoinEmulationBranchIfNeeded( querySpec, super::visitQuerySpec )
					&& !currentHelper.emulateFullJoinWithUnionIfNeeded( querySpec ) ) {
				super.visitQuerySpec( querySpec );

View on GitHub (pinned to fad1729dce)

Solutions

1. Apply the offset at the root query level (setFirstResult on the Query) instead of inside the subquery
2. Rewrite the skip-N subquery using ROW_NUMBER() in a derived table (native SQL if needed)
3. Remove the offset from the subquery and slice the result list in memory
4. Use a native SQL Anywhere query with TOP/START AT semantics

Example fix

// before (throws on SQL Anywhere)
"select p from Post p where p.authorId in (select a.id from Author a order by a.name offset 5)"

// after: offset moved to the root query
List<Post> posts = em.createQuery("select p from Post p where p.authorId in (select a.id from Author a)", Post.class)
    .setFirstResult(5).getResultList();

// SQL Anywhere: any offset in a non-root query part is untranslatable -- guard first
static boolean hqlSubqueryContainsOffset(String hql) {
    int depth = 0;
    String u = hql.toUpperCase();
    for (int i = 0; i < u.length(); i++) {
        char c = u.charAt(i);
        if (c == '(') depth++;
        else if (c == ')') depth--;
        else if (depth > 0 && u.startsWith("OFFSET", i)) return true;
    }
    return false;
}

if (dialect instanceof SQLAnywhereDialect && hqlSubqueryContainsOffset(hql)) {
    throw new IllegalArgumentException("Move the offset to the root query for SQL Anywhere");
}

try {
    return em.createQuery(hql, cls).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage().startsWith("Can't emulate offset clause in subquery")) {
        return rootPagedVariant(hql); // offset applied via setFirstResult on the outer query
    }
    throw e;
}

When it happens

Trigger: HQL with an offset inside a subquery on SQLAnywhereDialect, e.g. 'where id in (select x.id from X x order by x.k offset 5)' or criteria subqueries with setFirstResult-equivalent offsets; throws at SQL rendering time.

Common situations: Top-N/skip-N subquery patterns ported from other databases; Hibernate 6.6+ HQL 'offset' syntax used in subqueries; pagination logic pushed into subqueries during query refactoring.

Related errors

• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery

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