hibernate/hibernate-orm · error · UnsupportedOperationException

Can't emulate lateral join for query spec with aggregate fun

Error message

Can't emulate lateral join for query spec with aggregate function

What it means

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.

Source

Thrown at hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java:6967

	private QuerySpec stripToSelectClause(QuerySpec querySpec) {
		final var groupByExpressions = querySpec.getGroupByClauseExpressions();
		if ( groupByExpressions != null && !groupByExpressions.isEmpty() ) {
			throw new UnsupportedOperationException( "Can't emulate lateral join for query spec with group by clause" );
		}
		final Predicate havingRestrictions = querySpec.getHavingClauseRestrictions();
		if ( havingRestrictions != null && !havingRestrictions.isEmpty() ) {
			throw new UnsupportedOperationException( "Can't emulate lateral join for query spec with having clause" );
		}
		final var roots = querySpec.getFromClause().getRoots();
		final QuerySpec newQuerySpec = new QuerySpec( querySpec.isRoot(), roots.size() );
		for ( TableGroup root : roots ) {
			newQuerySpec.getFromClause().addRoot( root );
		}
		final SelectClause selectClause = querySpec.getSelectClause();
		for ( SqlSelection selection : selectClause.getSqlSelections() ) {
			if ( AggregateFunctionChecker.hasAggregateFunctions( selection.getExpression() ) ) {
				throw new UnsupportedOperationException( "Can't emulate lateral join for query spec with aggregate function" );
			}
			newQuerySpec.getSelectClause().addSqlSelection( selection );
		}
		return newQuerySpec;
	}

	private boolean needsLateralSortExpressionVirtualSelections(QuerySpec querySpec) {
		return !( ( querySpec.getSelectClause().getSqlSelections().size() == 1
						|| dialect.supportsRowValueConstructorSyntax() )
					&& dialect.supportsDistinctFromPredicate()
					&& isFetchFirstRowOnly( querySpec ) )
			&& !shouldEmulateLateralWithIntersect( querySpec )
			&& !dialect.supportsNestedSubqueryCorrelation()
			&& querySpec.hasOffsetOrFetchClause();
	}

	@Override
	public void visitTableGroup(TableGroup tableGroup) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Rewrite the correlated aggregate as a scalar subquery in the select/where clause of the outer query
2. Remove aggregate functions from the lateral part
3. Use a database with native LATERAL support
4. Use a native SQL query

Example fix

// before (no-lateral dialect)
List<Object[]> rows = session.createQuery(
    "select c, s.n from Customer c join lateral (select count(o) n from Ord o where o.customer = c) s").list();

// after
List<Object[]> rows = session.createQuery(
    "select c, (select count(o) from Ord o where o.customer = c) from Customer c").list();

if (!dialect.supportsLateral() && lateralPartHasAggregates(sq)) {
    // rewrite the correlated aggregate as a scalar subquery in select/where
}

try {
    query.list();
} catch (UnsupportedOperationException e) {
    if (String.valueOf(e.getMessage()).contains("lateral join for query spec with aggregate function")) {
        // fall back to scalar correlated subquery form
    } else throw e;
}

When it happens

Trigger: 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'.

Common situations: 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.

Related errors

• Can't emulate lateral join for query spec with group by clau
• Can't emulate lateral join for query spec with having clause
• Can't emulate lateral query group with limit/offset
• Can't emulate json_arrayagg filter clause when using 'null o
• Can't emulate json_objectagg 'with unique keys' clause.

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/b55daefadfbfa3bb. Report an issue: GitHub.