{"record":{"id":"8528fd91f6503622","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-8528fd","errorCode":null,"errorMessage":"Can't emulate offset clause in subquery","messagePattern":"Can't emulate offset clause in subquery","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/TeradataSqlAstTranslator.java","lineNumber":94,"sourceCode":"\t@Override\n\tprotected void renderFetchPlusOffsetExpression(\n\t\t\tExpression fetchClauseExpression,\n\t\t\tExpression offsetClauseExpression,\n\t\t\tint offset) {\n\t\trenderFetchPlusOffsetExpressionAsSingleParameter( fetchClauseExpression, offsetClauseExpression, offset );\n\t}\n\n\t@Override\n\tprotected void visitSqlSelections(SelectClause selectClause) {\n\t\trenderTopClause( (QuerySpec) getQueryPartStack().getCurrent(), true, true );\n\t\tsuper.visitSqlSelections( selectClause );\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\t// Teradata only supports the TOP clause\n\t\tif ( !queryPart.isRoot() && queryPart.getOffsetClauseExpression() != null ) {\n\t\t\tthrow new IllegalArgumentException( \"Can't emulate offset clause in subquery\" );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderComparison(Expression lhs, ComparisonOperator operator, Expression rhs) {\n\t\trenderComparisonEmulateIntersect( lhs, operator, rhs );\n\t}\n\n\t@Override\n\tprotected void renderSelectTupleComparison(\n\t\t\tList<SqlSelection> lhsExpressions,\n\t\t\tSqlTuple tuple,\n\t\t\tComparisonOperator operator) {\n\t\temulateSelectTupleComparison( lhsExpressions, tuple.getExpressions(), operator, true );\n\t}\n\n\t@Override\n\tprotected void renderPartitionItem(Expression expression) {","sourceCodeStart":76,"sourceCodeEnd":112,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/TeradataSqlAstTranslator.java#L76-L112","documentation":"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.","triggerScenarios":"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().","commonSituations":"Pagination frameworks (Spring Data Pageable) whose offsets propagate into subqueries; Teradata warehouses with queries ported from OFFSET/FETCH-capable databases.","solutions":["Keep offset/setFirstResult() on the outermost query only","Rewrite the subquery using TOP or QUALIFY/ROW_NUMBER-based limiting","Restructure to a join so no nested offset is required","Use a native Teradata query for the paginated statement"],"exampleFix":"// before\nselect e from Employee e\n where e.deptId in (select d.id from Dept d order by d.name offset 10)\n\n// after\nList<Employee> rows = em.createQuery(\n    \"select e from Employee e where e.deptId in (select d.id from Dept d)\", Employee.class)\n    .setFirstResult(10)\n    .getResultList();","handlingStrategy":"validation","validationCode":"Dialect d = sessionFactory.getJdbcServices().getDialect();\nif (d instanceof TeradataDialect) {\n    // build HQL without offsets in subqueries; paginate the root query only\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).setFirstResult(off).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"offset clause in subquery\")) {\n        // restructure query or fall back to native Teradata SQL\n    }\n    throw e;\n}","preventionTips":["On Teradata, express subquery limits with TOP or QUALIFY-style native constructs","Keep setFirstResult() on the outermost statement only","Run pagination tests against Teradata in CI if it is a supported backend"],"tags":["hibernate","teradata","pagination","offset","subquery"],"backgroundTag":"offset-in-subquery-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}