hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

Informix only allows the SKIP/OFFSET clause in the top-level query (and pagination is rendered as 'select first N skip M ...'). When InformixSqlAstTranslator.visitOffsetFetchClause() is asked to render a non-root QueryPart that carries an offset clause, there is no valid place to put it, so it throws IllegalArgumentException.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/InformixSqlAstTranslator.java:141

			renderExpressionAsLiteral( fetchExpression, getJdbcParameterBindings() );
		}
	}

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

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		// Informix only supports the SKIP clause in the top level query
		if ( !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {
			throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
		}
		// We use 'select first n' on Informix, so nothing to do here
	}

	@Override
	protected void beforeQueryGroup(QueryGroup queryGroup, QueryPart currentQueryPart) {
		if ( queryGroup.isRoot() && queryGroup.hasOffsetOrFetchClause() ) {
			append( "select ");
			renderFirstSkipClause( queryGroup.getOffsetClauseExpression(),
					queryGroup.getFetchClauseExpression() );
			append(  "* from " );
			append( OPEN_PARENTHESIS );
		}
	}

	@Override
	protected void afterQueryGroup(QueryGroup queryGroup, QueryPart currentQueryPart) {
		if ( queryGroup.isRoot() && queryGroup.hasOffsetOrFetchClause() ) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Restructure so pagination happens only at the top level: paginate the outer query, or use a derived-table/native query that Informix accepts
2. Replace the offset subquery with a non-offset equivalent (join on rowid, a stored function, or keyset predicates on the ordering column)
3. If the subquery result set is small, fetch it fully and trim the window in memory

Example fix

// before - offset inside subquery
select o from Order o
 where o.id in (select i.orderId from Item i order by i.createdAt offset 10 rows fetch first 5 rows only)

// after - keyset predicates instead of offset in the subquery
select o from Order o
 where o.id in (select i.orderId from Item i where i.createdAt > :cursor order by i.createdAt)
   and o.id in (select x.id from (native derived table limited to 5) x) -- or paginate the outer query

// reject offset/fetch appearing inside a subquery before execution
static boolean hasSubqueryOffset(String hql) {
    // crude but effective guard: offset/fetch before the matching close of an inner select
    return hql.toLowerCase().matches("(?s).*\\(\\s*select[^\u0000]*?(offset|skip)\\s+[0-9:?].*");
}
if ( session.getJdbcServices().getDialect() instanceof InformixDialect && hasSubqueryOffset(hql) ) {
    throw new IllegalArgumentException("Informix allows offset only at the top level");
}

static boolean isInformix(Dialect d) { return d instanceof InformixDialect; }

try {
    return session.createQuery(hql, Order.class).getResultList();
} catch (IllegalArgumentException e) {
    if ( String.valueOf(e.getMessage()).contains("offset clause in subquery") ) {
        // rewrite with keyset predicates on the ordering column and re-run
    }
    throw e;
}

When it happens

Trigger: An HQL query containing a subquery with its own offset, e.g. 'select o from Order o where o.id in (select i.id from Item i order by i.ts offset 10 rows fetch first 5 rows only)', executed on any Informix version — queryPart.isRoot() is false and getOffsetClauseExpression() != null.

Common situations: Keyset/limit-in-subquery pagination idioms written for PostgreSQL or MySQL and reused on Informix; Spring Data @Query with an ordered, limited subquery; refactoring a paged outer query into a subquery predicate.

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