hibernate/hibernate-orm · error · IllegalStateException

The JPA specification does not support subqueries having an

Error message

The JPA specification does not support subqueries having an order by clause. Please disable the JPA query compliance if you want to use this feature.

What it means

The JPA specification has no ORDER BY inside subqueries, so when hibernate.jpa.compliance.query=true Hibernate blocks it: every JpaSubQuery.orderBy(...) call runs validateComplianceOrderBy() and throws IllegalStateException. The same setting also guards multiselect and fetch/offset on subqueries, so one compliance flag can surface several of these errors at once.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/query/sqm/tree/spi/select/SqmSubQuery.java:501

		final SqmOrderByClause sqmOrderByClause = new SqmOrderByClause( orders.size() );
		for ( Order order : orders ) {
			sqmOrderByClause.addSortSpecification( (SqmSortSpecification) order );
		}
		getQueryPart().setOrderByClause( sqmOrderByClause );
		return this;
	}

	private void validateComplianceMultiselect() {
		if ( nodeBuilder().isJpaQueryComplianceEnabled() ) {
			throw new IllegalStateException(
					"The JPA specification does not support subqueries having multiple select items. " +
							"Please disable the JPA query compliance if you want to use this feature." );
		}
	}

	private void validateComplianceOrderBy() {
		if ( nodeBuilder().isJpaQueryComplianceEnabled() ) {
			throw new IllegalStateException(
					"The JPA specification does not support subqueries having an order by clause. " +
							"Please disable the JPA query compliance if you want to use this feature." );
		}
	}

	private void validateComplianceFetchOffset() {
		if ( nodeBuilder().isJpaQueryComplianceEnabled() ) {
			throw new IllegalStateException(
					"The JPA specification does not support subqueries having a fetch or offset clause. " +
							"Please disable the JPA query compliance if you want to use this feature." );
		}
	}

	@Nonnull
	@Override
	public <Y> SqmRoot<Y> correlate(@Nonnull Root<Y> parentRoot) {
		final SqmCorrelatedRoot<Y> correlated = ( (SqmRoot<Y>) parentRoot ).createCorrelation();
		getQuerySpec().addRoot( correlated );

View on GitHub (pinned to fad1729dce)

Solutions

Remove the ORDER BY from the subquery — without a limit, ordering inside a subquery rarely changes semantics; restructure so ordering happens in the outer query
If you rely on order + limit inside the subquery (top-N patterns), disable compliance: hibernate.jpa.compliance.query=false
Replace the ordered subquery with a CTE (with(...)) or a ROW_NUMBER-based join where the ordering lives in an ordered derivation

Example fix

// before (hibernate.jpa.compliance.query=true)
sub.orderBy(cb.desc(subRoot.get("createdAt"))); // IllegalStateException

// after — order in the outer query instead
query.orderBy(cb.desc(root.get("createdAt")));
// or keep the ordered subquery and set:
// hibernate.jpa.compliance.query=false

Defensive patterns

Strategy: validation

Validate before calling

boolean compliance = ((SqmCriteriaNodeBuilder) cb).isJpaQueryComplianceEnabled();
if (!compliance) { sub.orderBy(order); } // else: order in the outer query instead

Try / catch

try { sub.orderBy(orders); } catch (IllegalStateException e) { if (e.getMessage().contains("order by clause")) { query.orderBy(orders); } else throw e; }

Prevention

Assume subquery ORDER BY is unavailable when compliance mode is on; place ordering in the outer query
When enabling hibernate.jpa.compliance.query, audit criteria code for orderBy/multiselect/setFetch on subqueries — all three are guarded by the same flag
Prefer CTE/window-function rewrites for top-N patterns instead of ordered subqueries

When it happens

Trigger: hibernate.jpa.compliance.query=true combined with subquery.orderBy(order) or JpaSubQuery.sortSpecifications()/orderBy(List). Common in top-N-per-group patterns that order a subquery before limiting it.

Common situations: Enabling JPA compliance in an application that already used ordered subqueries (a Hibernate extension); porting native/HQL queries with ORDER BY inside IN(...) subqueries to criteria under a compliance-mandated configuration.

Related errors

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