{"record":{"id":"7cd9230f92cf3433","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-7cd923","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/IngresSqlAstTranslator.java","lineNumber":84,"sourceCode":"\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\tif ( supportsOffsetFetchClause() ) {\n\t\t\trenderOffsetFetchClause( queryPart, false );\n\t\t}\n\t\telse if ( !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":66,"sourceCodeEnd":102,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/IngresSqlAstTranslator.java#L66-L102","documentation":"IngresSqlAstTranslator.visitOffsetFetchClause() can only render native OFFSET/FETCH when the database version supports it (supportsOffsetFetchClause()). When it does not, and the offset appears in a non-root QueryPart (a subquery), there is no emulation available on Ingres, so it throws IllegalArgumentException.","triggerScenarios":"An HQL query with a subquery that declares its own offset (e.g., 'where x.id in (select y.id from Y y order by y.t offset 10)') executed on Ingres versions without native OFFSET/FETCH support — the translator must emulate the top-level offset but cannot emulate one inside a subquery.","commonSituations":"Porting pagination-in-subquery idioms from other databases to Ingres; legacy Ingres installations pinned to old server versions; shared query libraries that embed limited subqueries.","solutions":["Move pagination to the top-level query so only the outer query carries an offset","Replace the offset subquery with keyset-style predicates (where orderedColumn > :cursor)","Run the statement as native SQL with a formulation the Ingres server accepts"],"exampleFix":"// before\nselect e from Event e\n where e.sourceId in (select s.id from Source s order by s.lastSeen offset 20 rows fetch next 10 rows only)\n\n// after - keyset instead of offset inside the subquery\nselect e from Event e\n where e.sourceId in (select s.id from Source s where s.lastSeen < :cursor order by s.lastSeen desc)","handlingStrategy":"validation","validationCode":"static boolean hasSubqueryOffset(String hql) {\n    return hql.toLowerCase().matches(\"(?s).*\\\\(\\\\s*select[^\\u0000]*?(offset|fetch first)\\\\s.*\");\n}\nDialect d = session.getJdbcServices().getDialect();\nif ( d instanceof IngresDialect && hasSubqueryOffset(hql) ) {\n    // Ingres (pre-OFFSET/FETCH) cannot paginate inside subqueries - rewrite as keyset\n}","typeGuard":"static boolean isIngres(Dialect d) { return d instanceof IngresDialect; }","tryCatchPattern":"try {\n    return session.createQuery(hql, Event.class).getResultList();\n} catch (IllegalArgumentException e) {\n    if ( String.valueOf(e.getMessage()).contains(\"offset clause in subquery\") ) {\n        // rewrite subquery with keyset predicates and retry\n    }\n    throw e;\n}","preventionTips":["Keep pagination at the top level of every portable query","Prefer keyset predicates over OFFSET inside subqueries","Test query catalogs on the oldest supported server version of each database"],"tags":["hibernate","ingres","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"}