{"record":{"id":"fcebad96fe37557b","repo":"hibernate/hibernate-orm","slug":"can-t-render-offset-and-fetch-clause-for-subquery","errorCode":null,"errorMessage":"Can't render offset and fetch clause for subquery","messagePattern":"Can't render offset and fetch clause for subquery","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacySqlAstTranslator.java","lineNumber":185,"sourceCode":"\t\t\t\t\t\t\tresultRenderer.accept( e );\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\t\t\t);\n\t\t}\n\t\telse {\n\t\t\tsuper.visitAnsiCaseSimpleExpression( caseSimpleExpression, resultRenderer );\n\t\t}\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\t// Derby only supports the OFFSET and FETCH clause with ROWS\n\t\tassertRowsOnlyFetchClauseType( queryPart );\n\t\tif ( supportsOffsetFetchClause() ) {\n\t\t\trenderOffsetFetchClause( queryPart, true );\n\t\t}\n\t\telse if ( !getClauseStack().isEmpty() ) {\n\t\t\tthrow new IllegalArgumentException( \"Can't render offset and fetch clause for subquery\" );\n\t\t}\n\t}\n\n\t@Override\n\tprotected void renderFetchExpression(Expression fetchExpression) {\n\t\tif ( supportsParameterOffsetFetchExpression() ) {\n\t\t\tsuper.renderFetchExpression( fetchExpression );\n\t\t}\n\t\telse {\n\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}","sourceCodeStart":167,"sourceCodeEnd":203,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/DerbyLegacySqlAstTranslator.java#L167-L203","documentation":"Derby supports OFFSET/FETCH paging only from version 10.5 and has no LIMIT syntax or window functions to emulate it. DerbyLegacySqlAstTranslator.visitOffsetFetchClause can render paging at the top level of a query, but when a limit/offset appears inside a subquery (clause stack non-empty) on a Derby where the dialect reports no OFFSET/FETCH support, it throws IllegalArgumentException because no emulation exists.","triggerScenarios":"An HQL/Criteria query where the paginated QueryPart is a subquery - e.g. 'where x in (select ... order by ... limit 10)', a paginated subselect in a tuple comparison, or setMaxResults applied to a subquery - while running on Derby without OFFSET/FETCH support (pre-10.5 Derby with DerbyLegacyDialect). Top-level pagination on such Derby silently falls through instead.","commonSituations":"Old embedded Derby 10.4/10.1 runtimes in legacy swing/ODB applications; porting queries that page inside IN-subqueries from H2 to Derby; Hibernate upgrades that reclassify old Derby databases onto the legacy dialect.","solutions":["Move the pagination out of the subquery: page the outer query or split the IN-list into a two-step query (fetch ids with limit, then use them)","Upgrade the database to Derby 10.5+ and use DerbyDialect/ DerbyLegacyDialect so OFFSET/FETCH is reported as supported","Replace the paginated subquery with a join against a pre-paged derived table or temp table filled by a separate native query","Fetch the unpaginated subquery result and bound it in memory when the row count is provably small"],"exampleFix":"// before (HQL, subquery paging throws on Derby < 10.5)\nfrom Order o where o.customerId in (select id from Customer c order by c.name limit 10)\n\n// after (two steps: page first, then filter)\nList<Long> ids = session.createQuery(\"select id from Customer c order by c.name\", Long.class)\n    .setMaxResults(10).list();\nList<Order> orders = session.createQuery(\"from Order o where o.customerId in :ids\", Order.class)\n    .setParameter(\"ids\", ids).list();","handlingStrategy":"validation","validationCode":"// detect Derby without OFFSET/FETCH (pre-10.5) before running paged subqueries\nboolean legacyPaging = sessionFactory.getJdbcServices().getDialect() instanceof DerbyLegacyDialect;\nif (legacyPaging && queryHasPagedSubquery) {\n    // hoist pagination to the outer query or split into two queries\n}","typeGuard":"static boolean subqueryPaginationSafe(Dialect d) {\n    // Derby gained OFFSET/FETCH in 10.5; legacy dialect covers older versions\n    return !(d instanceof org.hibernate.community.dialect.DerbyLegacyDialect)\n        || d.getVersion().isSameOrAfter(10, 5);\n}","tryCatchPattern":"try {\n    results = session.createQuery(hql).list(); // hql pages inside a subquery\n} catch (IllegalArgumentException e) {\n    if (e.getMessage().contains(\"offset and fetch\")) {\n        // split: page the subquery ids first, then filter outer query by ids\n    } else throw e;\n}","preventionTips":["Never put limit/offset inside IN-subqueries in shared HQL - hoist paging to the outer query","Prefer two-step paging (fetch page of ids, then IN (:ids)) for portable code","Include the oldest supported Derby in the CI matrix so pre-10.5 limitations surface early"],"tags":["hibernate","derby","legacy-dialect","pagination","subquery","limit-offset"],"backgroundTag":"pagination-in-subquery-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}