{"record":{"id":"b55daefadfbfa3bb","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-lateral-join-for-query-spec-with-agg","errorCode":null,"errorMessage":"Can't emulate lateral join for query spec with aggregate function","messagePattern":"Can't emulate lateral join for query spec with aggregate function","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":6967,"sourceCode":"\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\n\t\t\t\t\t\t|| dialect.supportsRowValueConstructorSyntax() )\n\t\t\t\t\t&& dialect.supportsDistinctFromPredicate()\n\t\t\t\t\t&& isFetchFirstRowOnly( querySpec ) )\n\t\t\t&& !shouldEmulateLateralWithIntersect( querySpec )\n\t\t\t&& !dialect.supportsNestedSubqueryCorrelation()\n\t\t\t&& querySpec.hasOffsetOrFetchClause();\n\t}\n\n\t@Override\n\tpublic void visitTableGroup(TableGroup tableGroup) {","sourceCodeStart":6949,"sourceCodeEnd":6985,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L6949-L6985","documentation":"Third stripToSelectClause guard: while copying the lateral query spec's select items into the stripped query, AggregateFunctionChecker scans each select expression. If any selection contains an aggregate function (count, sum, avg, min, max, array_agg...), inlining would be semantically wrong, so this UnsupportedOperationException is thrown.","triggerScenarios":"Lateral join emulation on a non-LATERAL dialect where any select item of the lateral query spec contains an aggregate function - e.g. 'join lateral (select count(x), sum(x) from ... where <correlation>) s'.","commonSituations":"Correlated aggregate subqueries expressed as lateral joins; Hibernate 6.x implicit lateral from collection functions with aggregates; MySQL 5.7 / SQL Server emulated lateral paths; CI on H2 failing for PostgreSQL-targeted queries.","solutions":["Rewrite the correlated aggregate as a scalar subquery in the select/where clause of the outer query","Remove aggregate functions from the lateral part","Use a database with native LATERAL support","Use a native SQL query"],"exampleFix":"// before (no-lateral dialect)\nList<Object[]> rows = session.createQuery(\n    \"select c, s.n from Customer c join lateral (select count(o) n from Ord o where o.customer = c) s\").list();\n\n// after\nList<Object[]> rows = session.createQuery(\n    \"select c, (select count(o) from Ord o where o.customer = c) from Customer c\").list();","handlingStrategy":"fallback","validationCode":"if (!dialect.supportsLateral() && lateralPartHasAggregates(sq)) {\n    // rewrite the correlated aggregate as a scalar subquery in select/where\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 aggregate function\")) {\n        // fall back to scalar correlated subquery form\n    } else throw e;\n}","preventionTips":["Replace lateral aggregates with scalar subqueries on non-lateral dialects","Keep aggregate functions out of collection-function/lateral joins","Use native SQL for correlated aggregation when portability is not required"],"tags":["hibernate","lateral-join","aggregate-function","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"}