hibernate/hibernate-orm · error · IllegalArgumentException

Can't render offset and fetch clause for subquery

Error message

Can't render offset and fetch clause for subquery

What it means

Derby supports OFFSET/FETCH only from 10.5 and has no LIMIT or window functions. DerbySqlAstTranslator.visitOffsetFetchClause renders paging when the dialect supports OFFSET/FETCH; otherwise, if the paged query part sits inside a subquery (clause stack not empty), it throws IllegalArgumentException because Derby offers no way to express paging there.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbySqlAstTranslator.java:185

							resultRenderer.accept( e );
						}
					}
			);
		}
		else {
			super.visitAnsiCaseSimpleExpression( caseSimpleExpression, resultRenderer );
		}
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		// Derby only supports the OFFSET and FETCH clause with ROWS
		assertRowsOnlyFetchClauseType( queryPart );
		if ( supportsOffsetFetchClause() ) {
			renderOffsetFetchClause( queryPart, true );
		}
		else if ( !getClauseStack().isEmpty() ) {
			throw new IllegalArgumentException( "Can't render offset and fetch clause for subquery" );
		}
	}

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

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

View on GitHub (pinned to fad1729dce)

Solutions

1. Hoist the pagination to the outer query or split it into two queries (page the id subquery first, then filter the outer query by the returned ids)
2. Upgrade Derby to 10.5+ so OFFSET/FETCH is supported and renders even in subqueries
3. Rewrite the subquery without paging, e.g. join against a derived table produced by a separately executed paged native query
4. Apply in-memory sublisting when the subquery result is known to be small

Example fix

// before (HQL, throws on Derby without OFFSET/FETCH support)
from Product p where p.id in (select s.productId from Sale s order by s.date desc fetch first 5 rows only)

// after (page the subquery separately)
List<Long> topIds = session.createQuery("select s.productId from Sale s order by s.date desc", Long.class)
    .setMaxResults(5).list();
List<Product> products = session.createQuery("from Product p where p.id in :ids", Product.class)
    .setParameter("ids", topIds).list();

Dialect d = sessionFactory.getJdbcServices().getDialect();
boolean pagingSafe = !(d instanceof DerbyDialect) || d.getVersion().isSameOrAfter(10, 5);
if (!pagingSafe && containsPagedSubquery(hql)) {
    hql = hoistPaginationToOuterQuery(hql);
}

static boolean subqueryLimitSafe(Dialect d) {
    return !(d instanceof DerbyDialect) || d.getVersion().isSameOrAfter(10, 5);
}

try {
    rows = session.createQuery(hql).setMaxResults(n).list();
} catch (IllegalArgumentException e) {
    if ("Can't render offset and fetch clause for subquery".equals(e.getMessage())) {
        // re-run with paging moved to the outer query
    } else throw e;
}

When it happens

Trigger: A paginated QueryPart nested inside another query - 'where x in (select ... fetch first 10 rows only)', pageable subselects, HQL with limit/offset inside set operations or IN-predicates - executed on Derby where the translator's supportsOffsetFetchClause() is false (pre-10.5 Derby).

Common situations: Test suites that run the same query set against H2 and Derby, with only Derby failing on paged subqueries; keyset/limit patterns inside IN clauses ported from PostgreSQL; embedded Derby runtimes older than 10.5 in production appliances.

Related errors

• Can't render offset and fetch clause for subquery
• Can't emulate fetch clause type:
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset fetch clause in subquery

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/58f420ebe95fd928. Report an issue: GitHub.