hibernate/hibernate-orm · error · UnsupportedOperationException

Can't emulate lateral query group with limit/offset

Error message

Can't emulate lateral query group with limit/offset

What it means

To emulate LATERAL on databases without native support, Hibernate rewrites the lateral join as an EXISTS subquery correlated to the outer row. That rewrite needs the lateral part to be a plain QuerySpec; if the lateral query part is a QueryGroup (contains set operations like union) and a limit/offset emulation is also in play, the exists-based fallback cannot be built and this UnsupportedOperationException is thrown.

Source

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

				existsQuery.getFromClause().addRoot( subTableGroup );
				existsQuery.applyPredicate(
						new ComparisonPredicate(
								new SqlTuple( columnReferences, tableGroup.getModelPart() ),
								ComparisonOperator.NOT_DISTINCT_FROM,
								new SqlTuple( subColumnReferences, tableGroup.getModelPart() )
						)
				);

				return new ExistsPredicate(
					new SelectStatement( statement, existsQuery, emptyList() ),
					false,
						booleanType
				);
			}
			final QueryPart queryPart = statement.getQueryPart();
			if ( !( queryPart instanceof QuerySpec querySpec ) ) {
				// We can't use double nesting, but we need to add filter conditions, so fail if this is a query group
				throw new UnsupportedOperationException( "Can't emulate lateral query group with limit/offset" );
			}

			// The last possible way to emulate lateral subqueries is to check if the correlated subquery has a result for a row.
			// Note though, that if the subquery has a limit/offset, an additional condition is needed as can be seen below
			// ... x(c) on exists(select 1 from ... and sub_.c not distinct from x.c)

			final List<Expression> subExpressions = new ArrayList<>( columnNames.size() );
			for ( SqlSelection sqlSelection : querySpec.getSelectClause().getSqlSelections() ) {
				final Expression selectionExpression = sqlSelection.getExpression();
				final SqlTuple sqlTuple = getSqlTuple( selectionExpression );
				if ( sqlTuple == null ) {
					subExpressions.add( selectionExpression );
				}
				else {
					subExpressions.addAll( sqlTuple.getExpressions() );
				}
			}
			final QuerySpec existsQuery = new QuerySpec( false, querySpec.getFromClause().getRoots().size() );

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove the set operation from the lateral part (run each branch separately and combine in Java)
2. Move the limit/offset out of the lateral subquery into the outer query
3. Use a database/dialect with native LATERAL support (PostgreSQL, MySQL >= 8.0.14, Oracle 12c+, DB2)
4. Replace the lateral join with an explicit correlated subquery or native SQL

Example fix

// before (no-lateral dialect)
List<Post> posts = session.createQuery(
    "select p from Post p cross join lateral (select a from Article a where a.post=p union select b from Blog b where b.post=p) l limit 5").list();

// after
List<Post> posts = session.createQuery(
    "select p from Post p where p.id in (select a.post.id from Article a union select b.post.id from Blog b)").setMaxResults(5).list();

// Detect set operations + limit inside a lateral part before running on non-lateral dialects
if (!dialect.supportsLateral() && lateralPartContainsSetOperation(sq)) {
    // flatten to non-lateral form (in-subquery) before translation
}

try {
    query.list();
} catch (UnsupportedOperationException e) {
    if ("Can't emulate lateral query group with limit/offset".equals(e.getMessage())) {
        // rewrite without union/limit in the lateral part and retry
    } else throw e;
}

When it happens

Trigger: An HQL query using lateral semantics (cross join lateral, implicit lateral joins from array/collection functions like unnest/json_table-style, or join fetch with limit emulation) on a dialect without LATERAL support, where the lateral part is a set operation (union/intersect) - e.g. joining to a subquery that unions two selects.

Common situations: MySQL 5.7 / SQL Server dialects with collection-valued joins; joining an entity to a union subquery with @Limit; Hibernate 6.x where implicit lateral joins for collection functions became common; migrating queries from PostgreSQL to databases without lateral.

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 join for query spec with aggregate fun
• 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/b28a70366c76e0f4. Report an issue: GitHub.