hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate relational tuple subquery predicate with limit

Error message

Can't emulate relational tuple subquery predicate with limit/offset or set operations: 

What it means

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.

Source

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

		final QueryPart queryPart = selectStatement.getQueryPart();
		if ( queryPart instanceof QuerySpec querySpec
				&& queryPart.getFetchClauseExpression() == null
				&& queryPart.getOffsetClauseExpression() == null ) {
			// We can only emulate the tuple subquery predicate as exists predicate when there are no limit/offsets
			if ( negated ) {
				appendSql( "not " );
			}
			appendSql( "exists" );
			renderRelationalEmulationSubQuery(
					querySpec,
					lhsTuple,
					renderer,
					tupleComparisonOperator
			);
		}
		else {
			// TODO: We could use nested queries and use row numbers to emulate this
			throw new IllegalArgumentException(
					"Can't emulate relational tuple subquery predicate with limit/offset or set operations: " + predicate );
		}
	}

	protected <X extends Expression> void renderRelationalEmulationSubQuery(
			QuerySpec subQuery,
			X lhsTuple,
			SubQueryRelationalRestrictionEmulationRenderer<X> renderer,
			ComparisonOperator tupleComparisonOperator) {
		final QueryPart queryPartForRowNumbering = this.queryPartForRowNumbering;
		final int queryPartForRowNumberingClauseDepth = this.queryPartForRowNumberingClauseDepth;
		final boolean needsSelectAliases = this.needsSelectAliases;
		try {
			this.queryPartForRowNumbering = null;
			this.queryPartForRowNumberingClauseDepth = -1;
			this.needsSelectAliases = false;
			queryPartStack.push( subQuery );
			appendSql( OPEN_PARENTHESIS );

View on GitHub (pinned to fad1729dce)

Solutions

1. Remove limit/offset and set operations from the tuple subquery
2. Compare component-wise with individual scalar subqueries: a = (select x ...) and b = (select y ...)
3. Use a database with native row value constructor support
4. Use a native query

Example fix

// before (SQL Server)
... where (o.custId, o.seq) = (select c.id, c.seq from Cust c union select g.id, g.seq from Guest g)

// after
... where o.custId in (select c.id from Cust c union select g.id from Guest g)
      and o.seq in (select c.seq from Cust c union select g.seq from Guest g)

// On non-row-constructor dialects, reject tuple subquery predicates that contain limit/offset or set ops
if (!dialect.supportsRowValueConstructorSyntax()
        && tupleSubqueryHasLimitOrSetOperation(predicate)) {
    // expand to component-wise comparisons first
}

try {
    query.list();
} catch (IllegalArgumentException e) {
    if (String.valueOf(e.getMessage()).startsWith("Can't emulate relational tuple subquery predicate")) {
        // rewrite (a,b) = (select...) as component-wise scalar subqueries and retry
    } else throw e;
}

When it happens

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

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

Related errors

• Can't emulate quantified tuple subquery predicate with limit
• Can't emulate order preserving row constructor through strin
• Can't emulate equality preserving row constructor through st
• 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/c31662b1d55043b6. Report an issue: GitHub.