{"record":{"id":"d6e24826ae5130e8","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-quantified-tuple-subquery-predicate","errorCode":null,"errorMessage":"Can't emulate quantified tuple subquery predicate with limit/offset or set operations: ","messagePattern":"Can't emulate quantified 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":8176,"sourceCode":"\t\t\tPredicate predicate,\n\t\t\tSelectStatement selectStatement,\n\t\t\tSqlTuple lhsTuple,\n\t\t\tComparisonOperator tupleComparisonOperator) {\n\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 comparing against the top element when there are no limit/offsets\n\t\t\tlhsTuple.accept( this );\n\t\t\tappendSql( tupleComparisonOperator.sqlText() );\n\t\t\trenderQuantifiedEmulationSubQuery(\n\t\t\t\t\tquerySpec,\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 quantified tuple subquery predicate with limit/offset or set operations: \" + predicate );\n\t\t}\n\t}\n\n\tprotected void renderQuantifiedEmulationSubQuery(\n\t\t\tQuerySpec subQuery,\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 );\n\t\t\tvisitSelectClause( subQuery.getSelectClause() );\n\t\t\tvisitFromClause( subQuery.getFromClause() );","sourceCodeStart":8158,"sourceCodeEnd":8194,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/sql/ast/spi/AbstractSqlAstTranslator.java#L8158-L8194","documentation":"Quantified variant of the tuple subquery emulation: (a,b) = any (select x,y ...) or < all (select ...) on dialects without row constructor syntax is emulated by comparing against the subquery's top row after row-number wrapping (renderQuantifiedEmulationSubQuery). The comment above the throw records the constraint: this only works without limit/offset in the subquery part; otherwise IllegalArgumentException is thrown.","triggerScenarios":"HQL where a tuple is compared with = any/all/some against a subquery containing limit/fetch/offset or set operations, on a dialect lacking native row value constructor + quantified comparison support (e.g. SQL Server, MySQL < 8.0.19).","commonSituations":"any()/all() queries with composite keys on SQL Server; subqueries with union or pagination reused in quantified comparisons; porting PostgreSQL/standard SQL to MySQL.","solutions":["Remove limit/offset and set operations from the quantified subquery","Use a single-column comparison or component-wise comparisons","Use a database with native row value constructor support","Use a native query"],"exampleFix":"// before\n... where (o.custId, o.seq) = any (select v.custId, v.seq from Vip v fetch first 10 rows only)\n\n// after\n... where o.custId in (select v.custId from Vip v fetch first 10 rows only) and o.seq in (select v.seq from Vip v fetch first 10 rows only)","handlingStrategy":"fallback","validationCode":"if (!dialect.supportsRowValueConstructorSyntax()\n        && quantifiedSubqueryHasLimitOrSetOperation(predicate)) {\n    // rewrite = any/(tuple) as per-column in()/scalar comparisons\n}","typeGuard":null,"tryCatchPattern":"try {\n    query.list();\n} catch (IllegalArgumentException e) {\n    if (String.valueOf(e.getMessage()).startsWith(\"Can't emulate quantified tuple subquery predicate\")) {\n        // rewrite as component-wise in()/scalar predicates and retry\n    } else throw e;\n}","preventionTips":["Avoid any()/all() over multi-column subqueries with limit/offset or unions","Use single-column quantified comparisons on non-row-constructor dialects","Cover quantified comparison queries in per-dialect integration tests"],"tags":["hibernate","quantified-comparison","tuple-subquery","row-constructor","dialect-emulation"],"backgroundTag":"tuple-subquery-comparison-emulation","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}