{"record":{"id":"2134ea18cdeb2b83","repo":"hibernate/hibernate-orm","slug":"the-jpa-specification-does-not-support-subqueries-2134ea","errorCode":null,"errorMessage":"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.","messagePattern":"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\\.","errorType":"exception","errorClass":"IllegalStateException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/query/sqm/tree/spi/select/SqmSubQuery.java","lineNumber":501,"sourceCode":"\t\tfinal SqmOrderByClause sqmOrderByClause = new SqmOrderByClause( orders.size() );\n\t\tfor ( Order order : orders ) {\n\t\t\tsqmOrderByClause.addSortSpecification( (SqmSortSpecification) order );\n\t\t}\n\t\tgetQueryPart().setOrderByClause( sqmOrderByClause );\n\t\treturn this;\n\t}\n\n\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 );","sourceCodeStart":483,"sourceCodeEnd":519,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/query/sqm/tree/spi/select/SqmSubQuery.java#L483-L519","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before (hibernate.jpa.compliance.query=true)\nsub.orderBy(cb.desc(subRoot.get(\"createdAt\"))); // IllegalStateException\n\n// after — order in the outer query instead\nquery.orderBy(cb.desc(root.get(\"createdAt\")));\n// or keep the ordered subquery and set:\n// hibernate.jpa.compliance.query=false","handlingStrategy":"validation","validationCode":"boolean compliance = ((SqmCriteriaNodeBuilder) cb).isJpaQueryComplianceEnabled();\nif (!compliance) { sub.orderBy(order); } // else: order in the outer query instead","typeGuard":null,"tryCatchPattern":"try { sub.orderBy(orders); } catch (IllegalStateException e) { if (e.getMessage().contains(\"order by clause\")) { query.orderBy(orders); } else throw e; }","preventionTips":["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"],"tags":["hibernate","jpa-compliance","configuration","subquery","order-by"],"backgroundTag":"jpa-query-compliance","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}