{"record":{"id":"c31662b1d55043b6","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-relational-tuple-subquery-predicate","errorCode":null,"errorMessage":"Can't emulate relational tuple subquery predicate with limit/offset or set operations: ","messagePattern":"Can't emulate relational tuple subquery predicate with limit/offset or set operations: ","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java","lineNumber":8068,"sourceCode":"\t\tfinal QueryPart queryPart = selectStatement.getQueryPart();\n\t\tif ( queryPart instanceof QuerySpec querySpec\n\t\t\t\t&& queryPart.getFetchClauseExpression() == null\n\t\t\t\t&& queryPart.getOffsetClauseExpression() == null ) {\n\t\t\t// We can only emulate the tuple subquery predicate as exists predicate when there are no limit/offsets\n\t\t\tif ( negated ) {\n\t\t\t\tappendSql( \"not \" );\n\t\t\t}\n\t\t\tappendSql( \"exists\" );\n\t\t\trenderRelationalEmulationSubQuery(\n\t\t\t\t\tquerySpec,\n\t\t\t\t\tlhsTuple,\n\t\t\t\t\trenderer,\n\t\t\t\t\ttupleComparisonOperator\n\t\t\t);\n\t\t}\n\t\telse {\n\t\t\t// TODO: We could use nested queries and use row numbers to emulate this\n\t\t\tthrow new IllegalArgumentException(\n\t\t\t\t\t\"Can't emulate relational tuple subquery predicate with limit/offset or set operations: \" + predicate );\n\t\t}\n\t}\n\n\tprotected <X extends Expression> void renderRelationalEmulationSubQuery(\n\t\t\tQuerySpec subQuery,\n\t\t\tX lhsTuple,\n\t\t\tSubQueryRelationalRestrictionEmulationRenderer<X> renderer,\n\t\t\tComparisonOperator tupleComparisonOperator) {\n\t\tfinal QueryPart queryPartForRowNumbering = this.queryPartForRowNumbering;\n\t\tfinal int queryPartForRowNumberingClauseDepth = this.queryPartForRowNumberingClauseDepth;\n\t\tfinal boolean needsSelectAliases = this.needsSelectAliases;\n\t\ttry {\n\t\t\tthis.queryPartForRowNumbering = null;\n\t\t\tthis.queryPartForRowNumberingClauseDepth = -1;\n\t\t\tthis.needsSelectAliases = false;\n\t\t\tqueryPartStack.push( subQuery );\n\t\t\tappendSql( OPEN_PARENTHESIS );","sourceCodeStart":8050,"sourceCodeEnd":8086,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L8050-L8086","documentation":"For tuple-against-subquery comparisons ((a,b) = (select x,y ...)) on dialects without row value constructor syntax, Hibernate emulates by wrapping the subquery with row numbering and comparing against its top row. That emulation requires the subquery to be a flat QuerySpec; a limit/offset clause or set operations (union/intersect) inside it break the row-number trick, and this IllegalArgumentException (with the predicate dumped) is the explicit refusal.","triggerScenarios":"HQL tuple subquery comparison - where (a,b) = (select x,y from ...) or (a,b) < (select ...) - on SQL Server or other non-row-constructor dialects, where the subquery contains fetch first/limit/offset or a union/intersect.","commonSituations":"Composite-key lookups against subqueries on SQL Server; paginated subqueries reused inside where clauses; migrating queries from PostgreSQL where tuple syntax is native; union-based subqueries compared against id-class tuples.","solutions":["Remove limit/offset and set operations from the tuple subquery","Compare component-wise with individual scalar subqueries: a = (select x ...) and b = (select y ...)","Use a database with native row value constructor support","Use a native query"],"exampleFix":"// before (SQL Server)\n... where (o.custId, o.seq) = (select c.id, c.seq from Cust c union select g.id, g.seq from Guest g)\n\n// after\n... where o.custId in (select c.id from Cust c union select g.id from Guest g)\n      and o.seq in (select c.seq from Cust c union select g.seq from Guest g)","handlingStrategy":"fallback","validationCode":"// On non-row-constructor dialects, reject tuple subquery predicates that contain limit/offset or set ops\nif (!dialect.supportsRowValueConstructorSyntax()\n        && tupleSubqueryHasLimitOrSetOperation(predicate)) {\n    // expand to component-wise comparisons first\n}","typeGuard":null,"tryCatchPattern":"try {\n    query.list();\n} catch (IllegalArgumentException e) {\n    if (String.valueOf(e.getMessage()).startsWith(\"Can't emulate relational tuple subquery predicate\")) {\n        // rewrite (a,b) = (select...) as component-wise scalar subqueries and retry\n    } else throw e;\n}","preventionTips":["Keep tuple subqueries flat: no limit/offset, no union","Expand tuple comparisons into per-column subqueries on SQL Server/MySQL","Prefer row-constructor-capable databases for composite-key subquery logic"],"tags":["hibernate","tuple-subquery","row-constructor","dialect-emulation","comparison"],"backgroundTag":"tuple-subquery-comparison-emulation","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T14:17:55.899Z"}