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

Sybase ASE has no native OFFSET/FETCH, so SybaseSqlAstTranslator-style emulations rely on window functions and TOP. visitOffsetFetchClause in SybaseASESqlAstTranslator can emulate paging in the root query, and even an offset or a fetch alone in a subquery, but not BOTH an offset and a fetch clause inside a non-root query part -- it throws IllegalArgumentException('Can't emulate offset fetch clause in subquery'). The check only applies outside the full-join-emulation helper paths and after assertRowsOnlyFetchClauseType has validated the fetch type.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SybaseASESqlAstTranslator.java:357

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

	@Override
	protected void visitOrderBy(List<SortSpecification> sortSpecifications) {
		currentFullJoinEmulationHelper().renderOrderByIfNeeded( getCurrentQueryPart(), sortSpecifications, super::visitOrderBy );
	}

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

View on GitHub (pinned to fad1729dce)

Solutions

1. Move pagination to the outermost query: use setFirstResult/setMaxResults on the root Query instead of offset/fetch inside the subquery
2. Rewrite the top-N-per-group pattern using ROW_NUMBER() OVER (PARTITION BY ...) <= n in a derived table (native SQL if HQL cannot express it)
3. Fetch the subquery ids without paging and page the result in application memory
4. Precompute the paged ids into a temp table via native ASE SQL and join against it

Example fix

// before (throws on Sybase ASE)
"select o from Order o where o.customerId in (select c.id from Customer c order by c.name offset 10 fetch first 20 rows only)"

// after: page at the root, not in the subquery
List<Long> ids = em.createQuery("select c.id from Customer c order by c.name", Long.class)
    .setFirstResult(10).setMaxResults(20).getResultList();
List<Order> orders = em.createQuery("select o from Order o where o.customerId in :ids", Order.class)
    .setParameter("ids", ids).getResultList();

// Reject paging inside subqueries before executing on Sybase ASE
static boolean subqueryHasPaging(String hql) {
    // crude but effective guard for review gates; subquery = any '(' depth > 0
    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 SybaseASEDialect && subqueryHasPaging(hql)) {
    throw new IllegalArgumentException("Move offset/fetch to the root query for Sybase ASE");
}

try {
    return em.createQuery(hql, cls).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage().startsWith("Can't emulate offset fetch clause in subquery")) {
        return pagedRootAlternative(hql); // page at root or ROW_NUMBER rewrite
    }
    throw e;
}

When it happens

Trigger: HQL that puts paging inside a subquery with both offset and fetch, e.g. 'where x.id in (select y.id from Y y order by y.k offset 10 fetch first 5 rows only)' (or the equivalent HQL limit/offset syntax in a subquery), executed on SybaseASEDialect. Also reachable through criteria subqueries carrying both page parameters. Throws at SQL rendering time.

Common situations: Top-N-per-group queries (id in (select ... offset ... fetch ...)) written for PostgreSQL/standard SQL and run against ASE; query porting during a Sybase migration; Hibernate 6.6+ HQL paging syntax used inside 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/44e6490359678140. Report an issue: GitHub.