{"record":{"id":"aab7cfb1f49a3c13","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-aab7cf","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/RDMSOS2200SqlAstTranslator.java","lineNumber":55,"sourceCode":"\tprotected LockStrategy determineLockingStrategy(\n\t\t\tQuerySpec querySpec,\n\t\t\tLocking.FollowOn followOnLocking) {\n\t\treturn LockStrategy.NONE;\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\tif ( queryPart.isRoot() ) {\n\t\t\tif ( hasLimit() ) {\n\t\t\t\tprepareLimitOffsetParameters();\n\t\t\t\trenderFetch( getLimitParameter(), getOffsetParameter(), FetchClauseType.ROWS_ONLY );\n\t\t\t}\n\t\t\telse if ( queryPart.getFetchClauseExpression() != null ) {\n\t\t\t\trenderFetch( queryPart.getFetchClauseExpression(), queryPart.getOffsetClauseExpression(), queryPart.getFetchClauseType() );\n\t\t\t}\n\t\t}\n\t\telse if ( 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 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 boolean needsRowsToSkip() {\n\t\treturn true;\n\t}\n\n\t@Override\n\tprotected void renderComparison(Expression lhs, ComparisonOperator operator, Expression rhs) {","sourceCodeStart":37,"sourceCodeEnd":73,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/RDMSOS2200SqlAstTranslator.java#L37-L73","documentation":"RDMSOS2200SqlAstTranslator.visitOffsetFetchClause() only renders limit/offset for the root query part. On the Unisys RDMS for OS2200 dialect there is no way to emulate OFFSET inside a subquery/CTE/derived query part, so any non-root query part carrying an offset clause throws IllegalArgumentException during SQL translation.","triggerScenarios":"HQL/Criteria where a subquery, CTE or derived table contains limit/offset — e.g. 'where id in (select id order by x offset 10 rows fetch next 5 rows only)' — executed on RDMSOS2200.","commonSituations":"Portable query libraries that embed paginated subqueries; migrating an application onto a legacy Unisys OS/2200 database.","solutions":["Move pagination to the outer (root) query only","Rewrite the paginated subquery using row_number() over (...) filtering or a join","Precompute the limited key set in Java and bind it as a parameter list"],"exampleFix":"-- before\nselect o from Order o\nwhere o.id in (select s.id from SlowSource s order by s.rank offset 10 rows fetch next 5 rows only)\n\n-- after\nselect o from Order o where o.id in :pageIds","handlingStrategy":"try-catch","validationCode":"boolean hasSubqueryOffset(String hql) {\n    return hql != null && hql.toLowerCase( Locale.ROOT ).matches(\n        \"(?s).*\\\\b(offset|skip)\\\\b.*\" ) && hql.toLowerCase( Locale.ROOT ).contains( \" in (select\" );\n}\n// on RDMSOS2200: reject or rewrite such queries before execution","typeGuard":null,"tryCatchPattern":"try {\n    return query.list();\n}\ncatch ( IllegalArgumentException e ) {\n    if ( e.getMessage() != null && e.getMessage().contains( \"offset clause in subquery\" ) ) {\n        // restructure: paginate the outer query or precompute the id page\n        throw e;\n    }\n    throw e;\n}","preventionTips":["Keep limit/offset only on root queries on RDMSOS2200","Precompute paginated key sets in Java and bind them as parameters","Include a dialect capability matrix in query-builder code for exotic databases"],"tags":["rdms-os2200","pagination","subquery","offset-fetch"],"backgroundTag":"offset-in-subquery-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}