{"record":{"id":"b28a70366c76e0f4","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-lateral-query-group-with-limit-offse","errorCode":null,"errorMessage":"Can't emulate lateral query group with limit/offset","messagePattern":"Can't emulate lateral query group with limit/offset","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":6710,"sourceCode":"\t\t\t\texistsQuery.getFromClause().addRoot( subTableGroup );\n\t\t\t\texistsQuery.applyPredicate(\n\t\t\t\t\t\tnew ComparisonPredicate(\n\t\t\t\t\t\t\t\tnew SqlTuple( columnReferences, tableGroup.getModelPart() ),\n\t\t\t\t\t\t\t\tComparisonOperator.NOT_DISTINCT_FROM,\n\t\t\t\t\t\t\t\tnew SqlTuple( subColumnReferences, tableGroup.getModelPart() )\n\t\t\t\t\t\t)\n\t\t\t\t);\n\n\t\t\t\treturn new ExistsPredicate(\n\t\t\t\t\tnew SelectStatement( statement, existsQuery, emptyList() ),\n\t\t\t\t\tfalse,\n\t\t\t\t\t\tbooleanType\n\t\t\t\t);\n\t\t\t}\n\t\t\tfinal QueryPart queryPart = statement.getQueryPart();\n\t\t\tif ( !( queryPart instanceof QuerySpec querySpec ) ) {\n\t\t\t\t// We can't use double nesting, but we need to add filter conditions, so fail if this is a query group\n\t\t\t\tthrow new UnsupportedOperationException( \"Can't emulate lateral query group with limit/offset\" );\n\t\t\t}\n\n\t\t\t// The last possible way to emulate lateral subqueries is to check if the correlated subquery has a result for a row.\n\t\t\t// Note though, that if the subquery has a limit/offset, an additional condition is needed as can be seen below\n\t\t\t// ... x(c) on exists(select 1 from ... and sub_.c not distinct from x.c)\n\n\t\t\tfinal List<Expression> subExpressions = new ArrayList<>( columnNames.size() );\n\t\t\tfor ( SqlSelection sqlSelection : querySpec.getSelectClause().getSqlSelections() ) {\n\t\t\t\tfinal Expression selectionExpression = sqlSelection.getExpression();\n\t\t\t\tfinal SqlTuple sqlTuple = getSqlTuple( selectionExpression );\n\t\t\t\tif ( sqlTuple == null ) {\n\t\t\t\t\tsubExpressions.add( selectionExpression );\n\t\t\t\t}\n\t\t\t\telse {\n\t\t\t\t\tsubExpressions.addAll( sqlTuple.getExpressions() );\n\t\t\t\t}\n\t\t\t}\n\t\t\tfinal QuerySpec existsQuery = new QuerySpec( false, querySpec.getFromClause().getRoots().size() );","sourceCodeStart":6692,"sourceCodeEnd":6728,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L6692-L6728","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Remove the set operation from the lateral part (run each branch separately and combine in Java)","Move the limit/offset out of the lateral subquery into the outer query","Use a database/dialect with native LATERAL support (PostgreSQL, MySQL >= 8.0.14, Oracle 12c+, DB2)","Replace the lateral join with an explicit correlated subquery or native SQL"],"exampleFix":"// before (no-lateral dialect)\nList<Post> posts = session.createQuery(\n    \"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();\n\n// after\nList<Post> posts = session.createQuery(\n    \"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();","handlingStrategy":"fallback","validationCode":"// Detect set operations + limit inside a lateral part before running on non-lateral dialects\nif (!dialect.supportsLateral() && lateralPartContainsSetOperation(sq)) {\n    // flatten to non-lateral form (in-subquery) before translation\n}","typeGuard":null,"tryCatchPattern":"try {\n    query.list();\n} catch (UnsupportedOperationException e) {\n    if (\"Can't emulate lateral query group with limit/offset\".equals(e.getMessage())) {\n        // rewrite without union/limit in the lateral part and retry\n    } else throw e;\n}","preventionTips":["Keep lateral subqueries flat (single QuerySpec) on non-lateral databases","Move set operations and limits to the outer query","Prefer databases with native LATERAL for heavy lateral usage"],"tags":["hibernate","lateral-join","dialect-emulation","set-operations","query-group"],"backgroundTag":"lateral-join-emulation","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}