What causes "Can't emulate offset clause in subquery" in hibernate/hibernate-orm?

Remove operations that require physical tables (locking, native updates by table) from queries touching derived-mapped entities.

hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

DerivedTableReference wraps a derived from-element (subquery select item, VALUES clause, lateral subquery) which has no physical tables at all. Both resolveTableReference overloads unconditionally throw UnknownTableReferenceException: you cannot ask a subquery for a named base table. Hitting it means query translation tried to resolve a physical table relative to a derived table - usually locking, identity, or column-resolution logic that only makes sense against real tables.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/CacheSqlAstTranslator.java:71

	@Override
	protected void renderTopClause(QuerySpec querySpec, boolean addOffset, boolean needsParenthesis) {
		assertRowsOnlyFetchClauseType( querySpec );
		super.renderTopClause( querySpec, addOffset, needsParenthesis );
	}

	@Override
	protected void renderFetchPlusOffsetExpression(
			Expression fetchClauseExpression,
			Expression offsetClauseExpression,
			int offset) {
		renderFetchPlusOffsetExpressionAsSingleParameter( fetchClauseExpression, offsetClauseExpression, offset );
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		// Cache only supports the TOP clause
		if ( !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {
			throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
		}
	}

	@Override
	protected void renderComparison(Expression lhs, ComparisonOperator operator, Expression rhs) {
		renderComparisonEmulateIntersect( lhs, operator, rhs );
	}

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

	@Override
	protected void renderPartitionItem(Expression expression) {

View on GitHub (pinned to fad1729dce)

Solutions

Remove operations that require physical tables (locking, native updates by table) from queries touching derived-mapped entities.
Map the entity to a real table or a database view instead of a subselect where physical resolution is needed.
Restructure so column resolution targets the outer table group rather than going through the derived reference.
For CTE-based entities, run against dialects/configurations that keep resolution at the CTE label level, and upgrade Hibernate.

Example fix

// before: locking a subselect-mapped entity needs a physical table
List<V> rows = em.createQuery("select v from OrderView v where v.status = :s", OrderView.class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE) // OrderView is @Subselect-mapped
    .getResultList();

// after: lock the real entity, or read the view without locking
List<Order> rows = em.createQuery("select o from Order o where o.status = :s", Order.class)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)
    .getResultList();

Defensive patterns

Strategy: validation

Validate before calling

// Reject locking on derived-mapped entities before executing
boolean derivedMapped = "Subselect".equals(
    jpaEntityMeta.getAnnotation( "org.hibernate.annotations.Subselect" ) != null ? "Subselect" : null );
if ( hasLockMode && entityAnnotatedWithSubselect( entityClass ) ) {
    throw new IllegalArgumentException("Cannot lock a subselect-mapped entity: " + entityClass);
}

Try / catch

try {
    return em.createQuery(hql, type).setLockMode( LockModeType.PESSIMISTIC_WRITE ).getResultList();
} catch ( UnknownTableReferenceException e ) {
    if ( e.getMessage() != null && e.getMessage().contains("DerivedTableReferences") ) {
        // lock the real entity or re-read without locking
        return em.createQuery(hql, type).getResultList();
    }
    throw e;
}

Prevention

Never apply pessimistic locking or table-level operations to @Subselect/derived-mapped entities.
Map read models that need locking to real tables or updatable views.
Smoke-test derived-mapped entities against every query path used by the app.

When it happens

Trigger: Applying pessimistic locking (SELECT ... FOR UPDATE) or otherwise resolving physical tables against a derived from-element; entity mappings based on subqueries/values where later SQL AST phases request base-table resolution inside the derived reference; path expressions pushed below a lateral/derived boundary by a translator.

Common situations: Entities mapped via @Subselect or query-space derived tables combined with locking or native follow-up statements; CTE-referenced entities on dialect paths that resolve columns through the CTE's derived reference; custom dialect translators reordering resolution.

Related errors

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