{"record":{"id":"88f74d4cd658f6c2","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-lateral-join-for-query-spec-with-gro","errorCode":null,"errorMessage":"Can't emulate lateral join for query spec with group by clause","messagePattern":"Can't emulate lateral join for query spec with group by clause","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":6953,"sourceCode":"\t\t\treturn stripToSelectClause( querySpec );\n\t\t}\n\t\telse {\n\t\t\tthrow new AssertionFailure( \"Unexpected query part\" );\n\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;","sourceCodeStart":6935,"sourceCodeEnd":6971,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L6935-L6971","documentation":"LATERAL emulation on non-supporting dialects works by stripping the lateral query down to its select clause and inlining it into the outer query (stripToSelectClause). A GROUP BY clause inside the lateral query spec cannot be stripped this way - the grouping semantics would be lost - so this UnsupportedOperationException is raised.","triggerScenarios":"An HQL query whose lateral part (implicit lateral collection join or explicit lateral subquery) contains a group by clause, executed on a dialect without native LATERAL support (older MySQL, SQL Server variants, DB2 versions without lateral).","commonSituations":"Joining entity collections with aggregate grouping in the subquery; Hibernate 6.x array/collection function joins that imply lateral; migrating queries from PostgreSQL; tests running on H2/MySQL in CI while production uses PostgreSQL.","solutions":["Remove the group by from the lateral part or replace grouping with a subselect","Restructure the query so the grouped query is the outer query, not the lateral side","Use a database/dialect with native LATERAL support","Fall back to a native SQL query with the database's lateral syntax"],"exampleFix":"// before (no-lateral dialect)\nList<Dept> ds = session.createQuery(\n    \"select d from Dept d join lateral (select e.dept, count(e) c from Emp e where e.dept=d group by e.dept) s on 1=1\").list();\n\n// after\nList<Dept> ds = session.createQuery(\n    \"select d from Dept d where d.id in (select e.dept.id from Emp e group by e.dept.id)\").list();","handlingStrategy":"fallback","validationCode":"// If the dialect lacks lateral support, forbid group by in correlated subquery parts\nif (!dialect.supportsLateral() && lateralPartHasGroupBy(sq)) {\n    // restructure: aggregate outside or via scalar subquery\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 group by\")) {\n        // reroute to native SQL with the DB's lateral/grouping semantics\n    } else throw e;\n}","preventionTips":["Never put group by inside the lateral side of a join on non-lateral dialects","Move grouping into the outer query","Cover such queries with per-dialect integration tests"],"tags":["hibernate","lateral-join","group-by","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"}