hibernate/hibernate-orm · error · IllegalArgumentException

Can't emulate offset clause in subquery

Error message

Can't emulate offset clause in subquery

What it means

Teradata supports only the TOP clause for limiting rows. When a non-root query part (subquery or set-operation arm) declares an offset, the Teradata translator cannot emulate it and visitOffsetFetchClause throws IllegalArgumentException during SQL translation, regardless of Teradata version.

Source

Thrown at hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/TeradataSqlAstTranslator.java:94

	@Override
	protected void renderFetchPlusOffsetExpression(
			Expression fetchClauseExpression,
			Expression offsetClauseExpression,
			int offset) {
		renderFetchPlusOffsetExpressionAsSingleParameter( fetchClauseExpression, offsetClauseExpression, offset );
	}

	@Override
	protected void visitSqlSelections(SelectClause selectClause) {
		renderTopClause( (QuerySpec) getQueryPartStack().getCurrent(), true, true );
		super.visitSqlSelections( selectClause );
	}

	@Override
	public void visitOffsetFetchClause(QueryPart queryPart) {
		// Teradata only supports the TOP clause
		if ( !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {
			throw new IllegalArgumentException( "Can't emulate offset clause in subquery" );
		}
	}

	@Override
	protected void renderComparison(Expression lhs, ComparisonOperator operator, Expression rhs) {
		renderComparisonEmulateIntersect( lhs, operator, rhs );
	}

	@Override
	protected void renderSelectTupleComparison(
			List<SqlSelection> lhsExpressions,
			SqlTuple tuple,
			ComparisonOperator operator) {
		emulateSelectTupleComparison( lhsExpressions, tuple.getExpressions(), operator, true );
	}

	@Override
	protected void renderPartitionItem(Expression expression) {

View on GitHub (pinned to fad1729dce)

Solutions

1. Keep offset/setFirstResult() on the outermost query only
2. Rewrite the subquery using TOP or QUALIFY/ROW_NUMBER-based limiting
3. Restructure to a join so no nested offset is required
4. Use a native Teradata query for the paginated statement

Example fix

// before
select e from Employee e
 where e.deptId in (select d.id from Dept d order by d.name offset 10)

// after
List<Employee> rows = em.createQuery(
    "select e from Employee e where e.deptId in (select d.id from Dept d)", Employee.class)
    .setFirstResult(10)
    .getResultList();

Dialect d = sessionFactory.getJdbcServices().getDialect();
if (d instanceof TeradataDialect) {
    // build HQL without offsets in subqueries; paginate the root query only
}

try {
    return em.createQuery(hql).setFirstResult(off).getResultList();
} catch (IllegalArgumentException e) {
    if (e.getMessage() != null && e.getMessage().contains("offset clause in subquery")) {
        // restructure query or fall back to native Teradata SQL
    }
    throw e;
}

When it happens

Trigger: HQL/Criteria on TeradataDialect where a subquery has an offset, e.g. `where x in (select y from T order by y offset 10)` or a Criteria subquery with setFirstResult().

Common situations: Pagination frameworks (Spring Data Pageable) whose offsets propagate into subqueries; Teradata warehouses with queries ported from OFFSET/FETCH-capable databases.

Related errors

• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery
• Can't emulate offset clause in subquery

AI-assisted analysis of hibernate/hibernate-orm@fad1729dce (2026-08-22). Data as JSON: /api/errors/8528fd91f6503622. Report an issue: GitHub.