{"record":{"id":"a3131f7a43c5ad9c","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-lateral-join-for-query-spec-with-hav","errorCode":null,"errorMessage":"Can't emulate lateral join for query spec with having clause","messagePattern":"Can't emulate lateral join for query spec with having clause","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":6957,"sourceCode":"\t\t}\n\t}\n\n\tprivate QueryGroup stripToSelectClause(QueryGroup queryGroup) {\n\t\tfinal List<QueryPart> parts = new ArrayList<>( queryGroup.getQueryParts().size() );\n\t\tfor ( QueryPart queryPart : queryGroup.getQueryParts() ) {\n\t\t\tparts.add( stripToSelectClause( queryPart ) );\n\t\t}\n\t\treturn new QueryGroup( queryGroup.isRoot(), queryGroup.getSetOperator(), parts );\n\t}\n\n\tprivate QuerySpec stripToSelectClause(QuerySpec querySpec) {\n\t\tfinal var groupByExpressions = querySpec.getGroupByClauseExpressions();\n\t\tif ( groupByExpressions != null && !groupByExpressions.isEmpty() ) {\n\t\t\tthrow new UnsupportedOperationException( \"Can't emulate lateral join for query spec with group by clause\" );\n\t\t}\n\t\tfinal Predicate havingRestrictions = querySpec.getHavingClauseRestrictions();\n\t\tif ( havingRestrictions != null && !havingRestrictions.isEmpty() ) {\n\t\t\tthrow new UnsupportedOperationException( \"Can't emulate lateral join for query spec with having clause\" );\n\t\t}\n\t\tfinal var roots = querySpec.getFromClause().getRoots();\n\t\tfinal QuerySpec newQuerySpec = new QuerySpec( querySpec.isRoot(), roots.size() );\n\t\tfor ( TableGroup root : roots ) {\n\t\t\tnewQuerySpec.getFromClause().addRoot( root );\n\t\t}\n\t\tfinal SelectClause selectClause = querySpec.getSelectClause();\n\t\tfor ( SqlSelection selection : selectClause.getSqlSelections() ) {\n\t\t\tif ( AggregateFunctionChecker.hasAggregateFunctions( selection.getExpression() ) ) {\n\t\t\t\tthrow new UnsupportedOperationException( \"Can't emulate lateral join for query spec with aggregate function\" );\n\t\t\t}\n\t\t\tnewQuerySpec.getSelectClause().addSqlSelection( selection );\n\t\t}\n\t\treturn newQuerySpec;\n\t}\n\n\tprivate boolean needsLateralSortExpressionVirtualSelections(QuerySpec querySpec) {\n\t\treturn !( ( querySpec.getSelectClause().getSqlSelections().size() == 1","sourceCodeStart":6939,"sourceCodeEnd":6975,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L6939-L6975","documentation":"Same stripToSelectClause lateral emulation as the group-by case: a HAVING clause in the lateral query spec cannot be preserved when the query is stripped to its select clause and inlined into the outer query, so Hibernate throws this UnsupportedOperationException instead of silently changing query semantics.","triggerScenarios":"A lateral join emulation (dialect without LATERAL) where the lateral query spec carries a non-empty HAVING restriction, e.g. 'join lateral (select ... group by ... having count(*) > 1)' or any HQL that yields having inside the lateral part.","commonSituations":"Aggregating inside collection/lateral joins on MySQL 5.x or other non-lateral databases; migrating PostgreSQL queries with lateral + having; Hibernate 6.x implicit lateral collection joins.","solutions":["Move the having restriction into a where on a subselect or into the outer query","Remove the aggregate/having logic from the lateral part","Use a dialect with native LATERAL support","Write the query as native SQL"],"exampleFix":"// before\n... join lateral (select o.customer c, count(o) n from Ord o where o.customer=cust group by o.customer having count(o) > 5) s ...\n\n// after\n... where (select count(o) from Ord o where o.customer=cust) > 5 ...","handlingStrategy":"fallback","validationCode":"if (!dialect.supportsLateral() && lateralPartHasHaving(sq)) {\n    // convert having to a where predicate on a scalar subquery first\n}","typeGuard":null,"tryCatchPattern":"try {\n    query.list();\n} catch (UnsupportedOperationException e) {\n    if (String.valueOf(e.getMessage()).contains(\"lateral join for query spec with having clause\")) {\n        // push the filter into a correlated scalar subquery and retry\n    } else throw e;\n}","preventionTips":["Avoid having inside lateral/collection-join subqueries on non-lateral databases","Express aggregate filters as correlated subqueries in the outer where clause","Validate complex analytics queries on the production dialect"],"tags":["hibernate","lateral-join","having-clause","dialect-emulation","query-spec"],"backgroundTag":"lateral-join-emulation","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}