{"record":{"id":"ee11aaa492326a62","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-ee11aa","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/InformixSqlAstTranslator.java","lineNumber":141,"sourceCode":"\t\t\trenderExpressionAsLiteral( fetchExpression, getJdbcParameterBindings() );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderOffsetExpression(Expression offsetExpression) {\n\t\tif ( supportsParameterOffsetFetchExpression() ) {\n\t\t\tsuper.renderOffsetExpression( offsetExpression );\n\t\t}\n\t\telse {\n\t\t\trenderExpressionAsLiteral( offsetExpression, getJdbcParameterBindings() );\n\t\t}\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\t// Informix only supports the SKIP clause in the top level query\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\t// We use 'select first n' on Informix, so nothing to do here\n\t}\n\n\t@Override\n\tprotected void beforeQueryGroup(QueryGroup queryGroup, QueryPart currentQueryPart) {\n\t\tif ( queryGroup.isRoot() && queryGroup.hasOffsetOrFetchClause() ) {\n\t\t\tappend( \"select \");\n\t\t\trenderFirstSkipClause( queryGroup.getOffsetClauseExpression(),\n\t\t\t\t\tqueryGroup.getFetchClauseExpression() );\n\t\t\tappend(  \"* from \" );\n\t\t\tappend( OPEN_PARENTHESIS );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void afterQueryGroup(QueryGroup queryGroup, QueryPart currentQueryPart) {\n\t\tif ( queryGroup.isRoot() && queryGroup.hasOffsetOrFetchClause() ) {","sourceCodeStart":123,"sourceCodeEnd":159,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/InformixSqlAstTranslator.java#L123-L159","documentation":"Informix only allows the SKIP/OFFSET clause in the top-level query (and pagination is rendered as 'select first N skip M ...'). When InformixSqlAstTranslator.visitOffsetFetchClause() is asked to render a non-root QueryPart that carries an offset clause, there is no valid place to put it, so it throws IllegalArgumentException.","triggerScenarios":"An HQL query containing a subquery with its own offset, e.g. 'select o from Order o where o.id in (select i.id from Item i order by i.ts offset 10 rows fetch first 5 rows only)', executed on any Informix version — queryPart.isRoot() is false and getOffsetClauseExpression() != null.","commonSituations":"Keyset/limit-in-subquery pagination idioms written for PostgreSQL or MySQL and reused on Informix; Spring Data @Query with an ordered, limited subquery; refactoring a paged outer query into a subquery predicate.","solutions":["Restructure so pagination happens only at the top level: paginate the outer query, or use a derived-table/native query that Informix accepts","Replace the offset subquery with a non-offset equivalent (join on rowid, a stored function, or keyset predicates on the ordering column)","If the subquery result set is small, fetch it fully and trim the window in memory"],"exampleFix":"// before - offset inside subquery\nselect o from Order o\n where o.id in (select i.orderId from Item i order by i.createdAt offset 10 rows fetch first 5 rows only)\n\n// after - keyset predicates instead of offset in the subquery\nselect o from Order o\n where o.id in (select i.orderId from Item i where i.createdAt > :cursor order by i.createdAt)\n   and o.id in (select x.id from (native derived table limited to 5) x) -- or paginate the outer query","handlingStrategy":"validation","validationCode":"// reject offset/fetch appearing inside a subquery before execution\nstatic boolean hasSubqueryOffset(String hql) {\n    // crude but effective guard: offset/fetch before the matching close of an inner select\n    return hql.toLowerCase().matches(\"(?s).*\\\\(\\\\s*select[^\\u0000]*?(offset|skip)\\\\s+[0-9:?].*\");\n}\nif ( session.getJdbcServices().getDialect() instanceof InformixDialect && hasSubqueryOffset(hql) ) {\n    throw new IllegalArgumentException(\"Informix allows offset only at the top level\");\n}","typeGuard":"static boolean isInformix(Dialect d) { return d instanceof InformixDialect; }","tryCatchPattern":"try {\n    return session.createQuery(hql, Order.class).getResultList();\n} catch (IllegalArgumentException e) {\n    if ( String.valueOf(e.getMessage()).contains(\"offset clause in subquery\") ) {\n        // rewrite with keyset predicates on the ordering column and re-run\n    }\n    throw e;\n}","preventionTips":["Keep setFirstResult/setMaxResults on the outermost query only","Prefer keyset pagination (where orderedCol > :cursor) over offset in subquery predicates","Lint shared @Query strings for 'offset'/'skip' inside parentheses when Informix is a target"],"tags":["hibernate","informix","pagination","subquery","offset"],"backgroundTag":"subquery-pagination-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}