hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

SybaseLegacySqlAstTranslator (Sybase ASE) can limit rows only with TOP and has no way to emulate an OFFSET that appears in a subquery. Unlike the SQL Anywhere translator there is no version exemption: any non-root query part with an offset clause makes visitOffsetFetchClause throw IllegalArgumentException during translation.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseLegacySqlAstTranslator.java:206

	@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.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. Apply offset/setFirstResult() only to the root query and keep subqueries unpaginated
2. Replace the subquery offset with TOP (`select top 5 ...`) or a row-number derived table
3. Restructure the query as a join so no nested offset is needed
4. Use a native SQL query for that statement

Example fix

// before
select o from Order o
 where o.customerId in (select c.id from Customer c order by c.name offset 20)

// after
List<Order> rows = em.createQuery(
    "select o from Order o where o.customerId in (select c.id from Customer c)", Order.class)
    .setFirstResult(20)
    .getResultList();

// On Sybase ASE, reject query building that puts offsets in subqueries
Dialect d = sessionFactory.getJdbcServices().getDialect();
if (d instanceof SybaseLegacyDialect && hql.toLowerCase(Locale.ROOT).matches("(?s).*\\bfrom\\b[^)]*\\boffset\\b.*")) {
    throw new IllegalArgumentException("Subquery offset not supported on Sybase ASE");
}

try {
    return em.createQuery(hql).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("offset clause in subquery")) {
        // rewrite without nested offset or use native SQL, then retry once
    }
    throw e;
}

When it happens

Trigger: HQL/Criteria on SybaseLegacyDialect where a subquery or set-operation arm has an offset, e.g. `... in (select x.id from X x offset 10)` or a Criteria subquery with setFirstResult().

Common situations: Spring Data pagination reaching into subqueries; queries written against modern databases with OFFSET/FETCH then pointed at ASE; Hibernate 5 to 6 migrations where emulation behavior changed.

Related errors

• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset fetch 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/befef83791803615. Report an issue: GitHub.