{"record":{"id":"833750e8201593f6","repo":"hibernate/hibernate-orm","slug":"the-jpa-specification-does-not-support-subqueries-833750","errorCode":null,"errorMessage":"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.","messagePattern":"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\\.","errorType":"exception","errorClass":"IllegalStateException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/query/sqm/tree/spi/select/SqmSubQuery.java","lineNumber":509,"sourceCode":"\tprivate void validateComplianceMultiselect() {\n\t\tif ( nodeBuilder().isJpaQueryComplianceEnabled() ) {\n\t\t\tthrow new IllegalStateException(\n\t\t\t\t\t\"The JPA specification does not support subqueries having multiple select items. \" +\n\t\t\t\t\t\t\t\"Please disable the JPA query compliance if you want to use this feature.\" );\n\t\t}\n\t}\n\n\tprivate void validateComplianceOrderBy() {\n\t\tif ( nodeBuilder().isJpaQueryComplianceEnabled() ) {\n\t\t\tthrow new IllegalStateException(\n\t\t\t\t\t\"The JPA specification does not support subqueries having an order by clause. \" +\n\t\t\t\t\t\t\t\"Please disable the JPA query compliance if you want to use this feature.\" );\n\t\t}\n\t}\n\n\tprivate void validateComplianceFetchOffset() {\n\t\tif ( nodeBuilder().isJpaQueryComplianceEnabled() ) {\n\t\t\tthrow new IllegalStateException(\n\t\t\t\t\t\"The JPA specification does not support subqueries having a fetch or offset clause. \" +\n\t\t\t\t\t\t\t\"Please disable the JPA query compliance if you want to use this feature.\" );\n\t\t}\n\t}\n\n\t@Nonnull\n\t@Override\n\tpublic <Y> SqmRoot<Y> correlate(@Nonnull Root<Y> parentRoot) {\n\t\tfinal SqmCorrelatedRoot<Y> correlated = ( (SqmRoot<Y>) parentRoot ).createCorrelation();\n\t\tgetQuerySpec().addRoot( correlated );\n\t\treturn correlated;\n\t}\n\n\t@Nonnull\n\t@Override\n\tpublic <X, Y> SqmFrom<X, Y> correlate(@Nonnull From<X, Y> parentFrom) {\n\t\tif ( parentFrom instanceof Root<?> ) {\n\t\t\t//noinspection unchecked","sourceCodeStart":491,"sourceCodeEnd":527,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/query/sqm/tree/spi/select/SqmSubQuery.java#L491-L527","documentation":"JPA criteria subqueries support neither OFFSET nor FETCH/LIMIT clauses, so with hibernate.jpa.compliance.query=true Hibernate rejects them: JpaSubQuery.setOffset(...)/offset(...) and setFetch(...)/fetch(...) call validateComplianceFetchOffset(), which throws IllegalStateException for both paging operations. This is one of three compliance guards on subqueries (multiselect, orderBy, fetch/offset).","triggerScenarios":"hibernate.jpa.compliance.query=true plus subquery.setOffset(n)/setFetch(n) (or the JpaSubQuery#offset/#fetch variants) — typically pagination or top-N patterns pushed inside a subquery.","commonSituations":"Top-N-per-group queries (order + limit inside subquery) executed under a compliance-enabled configuration; migrating paginated native queries to criteria while a platform team enforces JPA compliance properties.","solutions":["Move the limit out of the subquery into the outer query's setFirstResult/setMaxResults, if semantics allow","Disable the guard for these queries: hibernate.jpa.compliance.query=false (JPA simply has no compliant way to limit a subquery)","Rewrite the limited subquery as a CTE (with(...)) or a ROW_NUMBER() window-function join, which keeps the outer query compliant"],"exampleFix":"// before (hibernate.jpa.compliance.query=true)\nsub.orderBy(cb.desc(subRoot.get(\"score\")));\nsub.setFetch(1); // IllegalStateException\n\n// after — top-N via window function in outer query, no subquery limit\nExpression<Long> rn = cb.function(\"row_number\", Long.class);\n// ... filter on rn <= n in the outer query, or set hibernate.jpa.compliance.query=false","handlingStrategy":"validation","validationCode":"boolean compliance = ((SqmCriteriaNodeBuilder) cb).isJpaQueryComplianceEnabled();\nif (!compliance) { sub.setFetch(limit); } else { /* use outer-query setMaxResults or a CTE rewrite */ }","typeGuard":null,"tryCatchPattern":"try { sub.setFetch(n); } catch (IllegalStateException e) { if (e.getMessage().contains(\"fetch or offset\")) { emQuery.setMaxResults(n); } else throw e; }","preventionTips":["Remember the JPA compliance flag guards subquery multiselect, orderBy AND fetch/offset together","Keep pagination in the outer query whenever possible","Cover compliance=true in CI so Hibernate-specific subquery limits fail at build time, not production"],"tags":["hibernate","jpa-compliance","configuration","subquery","pagination"],"backgroundTag":"jpa-query-compliance","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}