hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate fetch clause type:

Error message

Can't emulate fetch clause type: 

What it means

H2LegacySqlAstTranslator.visitOffsetFetchClause distinguishes rows-only paging from PERCENT / WITH TIES fetch clauses. PERCENT and WITH TIES arrived in H2 1.4.198 together with window functions; on older H2 versions (supportsOffsetFetchClausePercentWithTies() is false) there is no way to emulate them, so the translator throws IllegalArgumentException naming the FetchClauseType it cannot render.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/H2LegacySqlAstTranslator.java:243

	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		if ( isRowsOnlyFetchClauseType( queryPart ) ) {
			if ( supportsOffsetFetchClause() ) {
				renderOffsetFetchClause( queryPart, true );
			}
			else {
				renderLimitOffsetClause( queryPart );
			}
		}
		else {
			if ( supportsOffsetFetchClausePercentWithTies() ) {
				renderOffsetFetchClause( queryPart, true );
			}
			else {
				// FETCH PERCENT and WITH TIES were introduced along with window functions
				throw new IllegalArgumentException( "Can't emulate fetch clause type: " + queryPart.getFetchClauseType() );
			}
		}
	}

	@Override
	protected void renderSelectTupleComparison(
			List<SqlSelection> lhsExpressions,
			SqlTuple tuple,
			ComparisonOperator operator) {
		emulateSelectTupleComparison( lhsExpressions, tuple.getExpressions(), operator, true );
	}

	@Override
	public void visitInSubQueryPredicate(InSubQueryPredicate inSubQueryPredicate) {
		final SqlTuple lhsTuple;
		// As of 1.4.200 this is supported
		if ( getDialect().getVersion().isBefore( 1, 4, 200 )
				&& ( lhsTuple = SqlTupleContainer.getSqlTuple( inSubQueryPredicate.getTestExpression() ) ) != null

View on GitHub (pinned to fad1729dce)

Solutions

1. Upgrade the H2 dependency to 1.4.198+ (ideally 2.x with the non-legacy dialect) so PERCENT/WITH TIES render natively
2. Drop percent/with-ties: use plain 'fetch first N rows only' / setMaxResults(N), which renders as LIMIT on any H2 1.4.x
3. Emulate WITH TIES manually with a native H2 query using a subquery on the order-by rank (or window function if H2 >= 1.4.198), or page the rows-only superset and expand ties in Java
4. For PERCENT, compute the absolute row count first (count query) and apply a rows-only fetch of the computed N

Example fix

// before (HQL, throws on H2 < 1.4.198 legacy dialect)
select e from Employee e order by e.salary desc fetch first 10 percent rows with ties

// after (rows-only fetch, works on all H2 1.4.x)
select e from Employee e order by e.salary desc fetch first 10 rows only

// H2 legacy: PERCENT/WITH TIES need >= 1.4.198, rows-only fallback needs >= 1.4.195
Dialect d = sessionFactory.getJdbcServices().getDialect();
boolean percentWithTiesOk = !(d instanceof org.hibernate.community.dialect.H2LegacyDialect)
    || d.getVersion().isSameOrAfter(1, 4, 198);
if (!percentWithTiesOk && usesPercentOrWithTies(hql)) {
    hql = downgradeToRowsOnly(hql); // 'fetch first N rows only'
}

static boolean fetchClauseSafe(Dialect d, FetchClauseType type) {
    if (type == FetchClauseType.ROWS_ONLY) return true;
    return !(d instanceof org.hibernate.community.dialect.H2LegacyDialect) || d.getVersion().isSameOrAfter(1, 4, 198);
}

try {
    rows = session.createQuery(hql).list(); // 'fetch first 10 percent rows with ties'
} catch (IllegalArgumentException e) {
    if (e.getMessage().startsWith("Can't emulate fetch clause type")) {
        // retry with rows-only fetch or computed absolute count
    } else throw e;
}

When it happens

Trigger: HQL 'fetch first 20 percent rows only', 'fetch next 10 rows with ties', or 'limit 10 percent with ties' (or the Criteria/HQL API producing FetchClauseType.PERCENT_ONLY / PERCENT_WITH_TIES / ROWS_WITH_TIES) executed against H2 older than 1.4.198 under the legacy dialect. Plain rows-only paging falls back to LIMIT/OFFSET and never throws.

Common situations: Legacy applications pinned to old embedded H2 (1.4.195-1.4.197 or earlier) that adopt new pagination HQL; test data queries using with-ties for deterministic ordering; upgrading Hibernate without upgrading the H2 jar in legacy distributions.

Related errors

• Can't render offset and fetch clause for subquery
• Can't render offset and fetch clause for subquery
• Insert conflict 'do update' clause with constraint name is n
• Summarization is not supported by DBMS
• JDBC parameter value not bound -

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