hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate fetch clause type:

Error message

Can't emulate fetch clause type: 

What it means

When a dialect lacks native fetch-clause support, Hibernate emulates pagination by wrapping the query with row_number()/dense_rank(). assertRowsOnlyFetchClauseType checks any non-root query part (or a root without a limit context): if that query part carries a fetch clause type other than ROWS_ONLY (PERCENT_ONLY, ROWS_WITH_TIES, PERCENT_WITH_TIES), emulation is impossible and this IllegalArgumentException is thrown, because percent/ties semantics cannot be reproduced with row numbering.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:5038

		}
		if ( offsetExpression != null ) {
			final Stack<Clause> clauseStack = getClauseStack();
			appendSql( " offset " );
			clauseStack.push( Clause.OFFSET );
			try {
				renderOffsetExpression( offsetExpression );
			}
			finally {
				clauseStack.pop();
			}
		}
	}

	protected void assertRowsOnlyFetchClauseType(QueryPart queryPart) {
		if ( !queryPart.isRoot() || !hasLimit() ) {
			final FetchClauseType fetchClauseType = queryPart.getFetchClauseType();
			if ( fetchClauseType != null && fetchClauseType != FetchClauseType.ROWS_ONLY ) {
				throw new IllegalArgumentException( "Can't emulate fetch clause type: " + fetchClauseType );
			}
		}
	}

	protected QueryPart getQueryPartForRowNumbering() {
		return queryPartForRowNumbering;
	}

	protected boolean isRowNumberingCurrentQueryPart() {
		return queryPartForRowNumbering != null;
	}

	protected void emulateFetchOffsetWithWindowFunctions(QueryPart queryPart, boolean emulateFetchClause) {
		if ( queryPart.isRoot() && hasLimit() ) {
			prepareLimitOffsetParameters();
			emulateFetchOffsetWithWindowFunctions(
					queryPart,
					getOffsetParameter(),

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove WITH TIES / PERCENT and use plain 'fetch first N rows only' (ROWS_ONLY)
2. Move the fetch clause to the outermost (root) query part where the dialect/emulation can handle it
3. Use a database/dialect with native support for the desired fetch clause type (e.g. SQL Server 2022, Oracle 12c+, PostgreSQL 13+ for with-ties)
4. Emulate with-ties manually via rank() over(order by ...) subquery in a native query

Example fix

// before (MySQL dialect)
List<Employee> top = session.createQuery(
    "select e from Employee e where e.dept = :d fetch first 5 rows with ties order by e.salary desc").list();

// after
List<Employee> top = session.createNativeQuery(
    "select * from (select e.*, rank() over(order by e.salary desc) rk from emp e where e.dept=:d) t where rk <= 5").list();

// Reject WITH TIES / PERCENT fetch clauses before executing when the dialect lacks support
boolean ok = dialect.supportsFetchClause(FetchClauseType.ROWS_ONLY)
          || desiredFetchType == FetchClauseType.ROWS_ONLY;
if (!ok) { /* downgrade to rows-only or reject the request */ }

When it happens

Trigger: Using HQL fetch clause variants such as 'fetch first 10 percent rows only', 'fetch first 10 rows with ties', or 'limit 10 percent' inside a subquery (non-root query part) or in a context without an outer limit, on a dialect that does not natively support that fetch clause type (e.g. MySQL, H2 before 2.x, SQL Server pre-2022 for percent-with-ties).

Common situations: Porting HQL with WITH TIES / PERCENT from Oracle/PostgreSQL to MySQL; putting fetch-first in a subquery or CTE member; Criteria queries with FetchClauseType.JpaOnlyWithTies on unsupported databases; Hibernate 6.x where HQL limit syntax gained percent/ties options.

Related errors

• Can't emulate fetch clause type:
• Fetch clause may not be null
• Can't render offset and fetch clause for subquery
• Can't render offset and fetch clause for subquery
• Can't emulate offset clause in subquery

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