hibernate/hibernate-orm · error · UnsupportedOperationException

Paged queries not supported by {}

Error message

Paged queries not supported by {}

What it means

When a query carries paging (setMaxResults/setFirstResult), Hibernate asks the dialect's LimitHandler to rewrite the SQL via processSql; AbstractLimitHandler's default implementation throws UnsupportedOperationException naming the handler class because the dialect cannot express LIMIT/OFFSET. Handlers that never override processSql report supportsLimit()==false (the base default) - e.g. AbstractLimitHandler.NO_LIMIT used by legacy Derby - so requesting a page on such a dialect always fails. The base Dialect.getLimitHandler() itself throws a sibling error ('this dialect does not support query pagination').

Source

Thrown at hibernate-core/src/main/java/org/hibernate/dialect/pagination/AbstractLimitHandler.java:140

	 * Does this dialect require a one-based offset to be specified in the offset clause?
	 *
	 * @implNote The value passed into {@link AbstractLimitHandler#processSql(String, Limit)}
	 *           has a zero-based offset. Handlers which do not {@link #supportsVariableLimit}
	 *           should take care to perform any needed first-row-conversion calls prior to
	 *           injecting the limit values into the SQL string.
	 *
	 * @param zeroBasedFirstResult The user-supplied, zero-based first row offset.
	 *
	 * @return The resulting offset, adjusted to one-based if necessary.
	 */
	public int convertToFirstRowValue(int zeroBasedFirstResult) {
		return zeroBasedFirstResult;
	}


	@Override
	public String processSql(String sql, Limit limit) {
		throw new UnsupportedOperationException( "Paged queries not supported by " + getClass().getName() );
	}

	@Override
	public int bindLimitParametersAtStartOfQuery(Limit limit, PreparedStatement statement, int index)
			throws SQLException {
		return bindLimitParametersFirst()
				? bindLimitParameters( limit, statement, index )
				: 0;
	}

	@Override
	public int bindLimitParametersAtEndOfQuery(Limit limit, PreparedStatement statement, int index)
			throws SQLException {
		return !bindLimitParametersFirst()
				? bindLimitParameters( limit, statement, index )
				: 0;
	}

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove setMaxResults/setFirstResult for that database and page in memory (fetch then subList) or with a windowed/native query the database supports
2. Use a database/dialect combination with real pagination support for paged views
3. If you own the dialect, override processSql (e.g. extend AbstractSimpleLimitHandler or OffsetFetchLimitHandler) to emit the database's limit syntax
4. Guard the UI/API layer: detect unsupported paging early via dialect.getLimitHandler().supportsLimit() instead of failing at query execution

Example fix

// before (throws on dialects whose LimitHandler cannot rewrite SQL)
List<Order> page = em.createQuery("select o from Order o", Order.class)
        .setFirstResult(page * size).setMaxResults(size).getResultList();

// after: page in memory when the dialect cannot paginate
List<Order> all = em.createQuery("select o from Order o", Order.class).getResultList();
List<Order> page = all.subList(page * size, Math.min((page + 1) * size, all.size()));

Dialect dialect = sessionFactory.getJdbcServices().getDialect();
boolean canPage;
try {
    canPage = dialect.getLimitHandler().supportsLimit();
} catch (UnsupportedOperationException e) {
    canPage = false; // dialect has no limit handler at all
}
if (!canPage) { /* page in memory or fail early with a clear message */ }

static boolean supportsPaging(SessionFactory sf) {
    try {
        return sf.getJdbcServices().getDialect().getLimitHandler().supportsLimit();
    } catch (UnsupportedOperationException e) {
        return false;
    }
}

try {
    return query.setFirstResult(offset).setMaxResults(size).getResultList();
} catch (UnsupportedOperationException e) {
    if (e.getMessage() != null && e.getMessage().startsWith("Paged queries not supported")) {
        // fall back to in-memory paging or reject paging for this datasource
    }
    throw e;
}

When it happens

Trigger: query.setMaxResults(n) and/or setFirstResult(n) (including Spring Data PageRequest / Pageable repositories) on a dialect whose LimitHandler is AbstractLimitHandler.NO_LIMIT (DerbyLegacyDialect) or a custom handler extending AbstractLimitHandler without overriding processSql; executing the same paging query that worked on PostgreSQL against such a database.

Common situations: Applications migrated off deprecated Derby dialects onto DerbyLegacyDialect; custom/legacy dialects for niche databases with no LIMIT syntax; integration tests running against embedded databases lacking pagination; shared repository code assuming universal paging support.

Related errors

• Scrollable result sets are not enabled
• Can't render offset and fetch clause for subquery
• Can't render offset and fetch clause for subquery
• Can't emulate fetch clause type:
• Summarization is not supported by DBMS

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