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 paging only from version 10.5 and has no LIMIT syntax or window functions to emulate it. DerbyLegacySqlAstTranslator.visitOffsetFetchClause can render paging at the top level of a query, but when a limit/offset appears inside a subquery (clause stack non-empty) on a Derby where the dialect reports no OFFSET/FETCH support, it throws IllegalArgumentException because no emulation exists.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacySqlAstTranslator.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. Move the pagination out of the subquery: page the outer query or split the IN-list into a two-step query (fetch ids with limit, then use them)
2. Upgrade the database to Derby 10.5+ and use DerbyDialect/ DerbyLegacyDialect so OFFSET/FETCH is reported as supported
3. Replace the paginated subquery with a join against a pre-paged derived table or temp table filled by a separate native query
4. Fetch the unpaginated subquery result and bound it in memory when the row count is provably small

Example fix

// before (HQL, subquery paging throws on Derby < 10.5)
from Order o where o.customerId in (select id from Customer c order by c.name limit 10)

// after (two steps: page first, then filter)
List<Long> ids = session.createQuery("select id from Customer c order by c.name", Long.class)
    .setMaxResults(10).list();
List<Order> orders = session.createQuery("from Order o where o.customerId in :ids", Order.class)
    .setParameter("ids", ids).list();

// detect Derby without OFFSET/FETCH (pre-10.5) before running paged subqueries
boolean legacyPaging = sessionFactory.getJdbcServices().getDialect() instanceof DerbyLegacyDialect;
if (legacyPaging && queryHasPagedSubquery) {
    // hoist pagination to the outer query or split into two queries
}

static boolean subqueryPaginationSafe(Dialect d) {
    // Derby gained OFFSET/FETCH in 10.5; legacy dialect covers older versions
    return !(d instanceof org.hibernate.community.dialect.DerbyLegacyDialect)
        || d.getVersion().isSameOrAfter(10, 5);
}

try {
    results = session.createQuery(hql).list(); // hql pages inside a subquery
} catch (IllegalArgumentException e) {
    if (e.getMessage().contains("offset and fetch")) {
        // split: page the subquery ids first, then filter outer query by ids
    } else throw e;
}

When it happens

Trigger: An HQL/Criteria query where the paginated QueryPart is a subquery - e.g. 'where x in (select ... order by ... limit 10)', a paginated subselect in a tuple comparison, or setMaxResults applied to a subquery - while running on Derby without OFFSET/FETCH support (pre-10.5 Derby with DerbyLegacyDialect). Top-level pagination on such Derby silently falls through instead.

Common situations: Old embedded Derby 10.4/10.1 runtimes in legacy swing/ODB applications; porting queries that page inside IN-subqueries from H2 to Derby; Hibernate upgrades that reclassify old Derby databases onto the legacy dialect.

Related errors

• Can't render offset and fetch clause for subquery
• Can't emulate fetch clause type:
• field type not supported on Derby: " + unit
• format() function not supported on Derby
• Can't emulate offset clause in subquery

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