hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

IngresSqlAstTranslator.visitOffsetFetchClause() can only render native OFFSET/FETCH when the database version supports it (supportsOffsetFetchClause()). When it does not, and the offset appears in a non-root QueryPart (a subquery), there is no emulation available on Ingres, so it throws IllegalArgumentException.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/IngresSqlAstTranslator.java:84

	}

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

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		if ( supportsOffsetFetchClause() ) {
			renderOffsetFetchClause( queryPart, false );
		}
		else 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. Move pagination to the top-level query so only the outer query carries an offset
2. Replace the offset subquery with keyset-style predicates (where orderedColumn > :cursor)
3. Run the statement as native SQL with a formulation the Ingres server accepts

Example fix

// before
select e from Event e
 where e.sourceId in (select s.id from Source s order by s.lastSeen offset 20 rows fetch next 10 rows only)

// after - keyset instead of offset inside the subquery
select e from Event e
 where e.sourceId in (select s.id from Source s where s.lastSeen < :cursor order by s.lastSeen desc)

static boolean hasSubqueryOffset(String hql) {
    return hql.toLowerCase().matches("(?s).*\\(\\s*select[^\u0000]*?(offset|fetch first)\\s.*");
}
Dialect d = session.getJdbcServices().getDialect();
if ( d instanceof IngresDialect && hasSubqueryOffset(hql) ) {
    // Ingres (pre-OFFSET/FETCH) cannot paginate inside subqueries - rewrite as keyset
}

static boolean isIngres(Dialect d) { return d instanceof IngresDialect; }

try {
    return session.createQuery(hql, Event.class).getResultList();
} catch (IllegalArgumentException e) {
    if ( String.valueOf(e.getMessage()).contains("offset clause in subquery") ) {
        // rewrite subquery with keyset predicates and retry
    }
    throw e;
}

When it happens

Trigger: An HQL query with a subquery that declares its own offset (e.g., 'where x.id in (select y.id from Y y order by y.t offset 10)') executed on Ingres versions without native OFFSET/FETCH support — the translator must emulate the top-level offset but cannot emulate one inside a subquery.

Common situations: Porting pagination-in-subquery idioms from other databases to Ingres; legacy Ingres installations pinned to old server versions; shared query libraries that embed limited subqueries.

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