hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

Hibernate's SQL Anywhere translator can emulate row limiting only through the TOP clause. For dialect versions before 9, a non-root query part (a subquery or a set-operation arm) that declares an OFFSET cannot be rendered at all, so visitOffsetFetchClause throws IllegalArgumentException during SQL translation rather than producing wrong results. The guard fires when the dialect version isBefore(9), the query part is not root, useOffsetFetchClause is true for it, and an offset expression is present.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseAnywhereSqlAstTranslator.java:174

	@Override
	protected void renderTopClause(QuerySpec querySpec, boolean addOffset, boolean needsParenthesis) {
		assertRowsOnlyFetchClauseType( querySpec );
		super.renderTopClause( querySpec, addOffset, needsParenthesis );
	}

	@Override
	protected void renderTopStartAtClause(QuerySpec querySpec) {
		assertRowsOnlyFetchClauseType( querySpec );
		super.renderTopStartAtClause( querySpec );
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		// Sybase Anywhere only supports the TOP clause
		if ( getDialect().getVersion().isBefore( 9 ) && !queryPart.isRoot()
				&& useOffsetFetchClause( queryPart ) && queryPart.getOffsetClauseExpression() != null ) {
			throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
		}
	}

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

	@Override
	protected void renderSelectTupleComparison(
			List<SqlSelection> lhsExpressions,
			SqlTuple tuple,
			ComparisonOperator operator) {
		emulateSelectTupleComparison( lhsExpressions, tuple.getExpressions(), operator, true );
	}

	@Override
	protected void renderPartitionItem(Expression expression) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Upgrade SQL Anywhere and configure the dialect with version 9+ so offsets render natively
2. Apply setFirstResult()/offset only to the outermost query and leave subqueries unpaginated
3. Rewrite the subquery to use TOP-based limiting or a row-number derived table join
4. Fall back to a native SQL query for the paginated statement

Example fix

// before
select e from Employee e
where e.id in (select a.empId from Audit a order by a.at offset 5)

// after: offset only on the root query
List<Employee> rows = em.createQuery(
    "select e from Employee e where e.id in (select a.empId from Audit a)", Employee.class)
    .setFirstResult(5)
    .getResultList();

// Before building the query: on SQL Anywhere < 9 keep offsets off subqueries
Dialect d = sessionFactory.getJdbcServices().getDialect();
if (d instanceof SybaseAnywhereDialect sad && sad.getVersion().isBefore(9)) {
    // generate HQL without 'offset' inside subqueries;
    // apply setFirstResult() only on the root query
}

try {
    return em.createQuery(hql).setFirstResult(off).setMaxResults(lim).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("offset clause in subquery")) {
        // restructure: move offset to the root query or switch to native SQL, then retry once
        throw new UnsupportedOperationException("Rewrite needed: subquery offset", e);
    }
    throw e;
}

When it happens

Trigger: Running HQL/Criteria on SybaseAnywhereDialect with version < 9 where a subquery carries an offset, e.g. `where e.id in (select x.id from X x order by x.id offset 10)`, a Criteria subquery with setFirstResult(), or a UNION arm that has an offset clause.

Common situations: Legacy SQL Anywhere 8/9 installations or a dialect built with an old explicit DatabaseVersion; Spring Data/Pageable pagination that gets pushed into subqueries; queries migrated from databases with native OFFSET/FETCH.

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