{"record":{"id":"58f420ebe95fd928","repo":"hibernate/hibernate-orm","slug":"can-t-render-offset-and-fetch-clause-for-subquery-58f420","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/DerbySqlAstTranslator.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/DerbySqlAstTranslator.java#L167-L203","documentation":"Derby supports OFFSET/FETCH only from 10.5 and has no LIMIT or window functions. DerbySqlAstTranslator.visitOffsetFetchClause renders paging when the dialect supports OFFSET/FETCH; otherwise, if the paged query part sits inside a subquery (clause stack not empty), it throws IllegalArgumentException because Derby offers no way to express paging there.","triggerScenarios":"A paginated QueryPart nested inside another query - 'where x in (select ... fetch first 10 rows only)', pageable subselects, HQL with limit/offset inside set operations or IN-predicates - executed on Derby where the translator's supportsOffsetFetchClause() is false (pre-10.5 Derby).","commonSituations":"Test suites that run the same query set against H2 and Derby, with only Derby failing on paged subqueries; keyset/limit patterns inside IN clauses ported from PostgreSQL; embedded Derby runtimes older than 10.5 in production appliances.","solutions":["Hoist the pagination to the outer query or split it into two queries (page the id subquery first, then filter the outer query by the returned ids)","Upgrade Derby to 10.5+ so OFFSET/FETCH is supported and renders even in subqueries","Rewrite the subquery without paging, e.g. join against a derived table produced by a separately executed paged native query","Apply in-memory sublisting when the subquery result is known to be small"],"exampleFix":"// before (HQL, throws on Derby without OFFSET/FETCH support)\nfrom Product p where p.id in (select s.productId from Sale s order by s.date desc fetch first 5 rows only)\n\n// after (page the subquery separately)\nList<Long> topIds = session.createQuery(\"select s.productId from Sale s order by s.date desc\", Long.class)\n    .setMaxResults(5).list();\nList<Product> products = session.createQuery(\"from Product p where p.id in :ids\", Product.class)\n    .setParameter(\"ids\", topIds).list();","handlingStrategy":"validation","validationCode":"Dialect d = sessionFactory.getJdbcServices().getDialect();\nboolean pagingSafe = !(d instanceof DerbyDialect) || d.getVersion().isSameOrAfter(10, 5);\nif (!pagingSafe && containsPagedSubquery(hql)) {\n    hql = hoistPaginationToOuterQuery(hql);\n}","typeGuard":"static boolean subqueryLimitSafe(Dialect d) {\n    return !(d instanceof DerbyDialect) || d.getVersion().isSameOrAfter(10, 5);\n}","tryCatchPattern":"try {\n    rows = session.createQuery(hql).setMaxResults(n).list();\n} catch (IllegalArgumentException e) {\n    if (\"Can't render offset and fetch clause for subquery\".equals(e.getMessage())) {\n        // re-run with paging moved to the outer query\n    } else throw e;\n}","preventionTips":["Apply setMaxResults/setFirstResult only to the top-level query","Smoke-test paged queries against every database in the deployment matrix","Track the Derby version floor in the project's compatibility docs"],"tags":["hibernate","derby","pagination","subquery","limit-offset","fetch-first"],"backgroundTag":"pagination-in-subquery-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}