{"record":{"id":"44e6490359678140","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-fetch-clause-in-subquery-44e649","errorCode":null,"errorMessage":"Can't emulate offset fetch clause in subquery","messagePattern":"Can't emulate offset fetch clause in subquery","errorType":"exception","errorClass":"IllegalArgumentException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SybaseASESqlAstTranslator.java","lineNumber":357,"sourceCode":"\tprotected void visitValuesList(List<Values> valuesList) {\n\t\tvisitValuesListEmulateSelectUnion( valuesList );\n\t}\n\n\t@Override\n\tpublic void visitValuesTableReference(ValuesTableReference tableReference) {\n\t\tappend( '(' );\n\t\tvisitValuesListEmulateSelectUnion( tableReference.getValuesList() );\n\t\tappend( ')' );\n\t\trenderDerivedTableReferenceIdentificationVariable( tableReference );\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\tif ( !currentFullJoinEmulationHelper().isFullJoinEmulationQueryPart( queryPart ) ) {\n\t\t\tassertRowsOnlyFetchClauseType( queryPart );\n\t\t\tif ( !queryPart.isRoot() && queryPart.hasOffsetOrFetchClause() ) {\n\t\t\t\tif ( queryPart.getFetchClauseExpression() != null && queryPart.getOffsetClauseExpression() != null ) {\n\t\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate offset fetch clause in subquery\" );\n\t\t\t\t}\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tprotected void visitOrderBy(List<SortSpecification> sortSpecifications) {\n\t\tcurrentFullJoinEmulationHelper().renderOrderByIfNeeded( getCurrentQueryPart(), sortSpecifications, super::visitOrderBy );\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}","sourceCodeStart":339,"sourceCodeEnd":375,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SybaseASESqlAstTranslator.java#L339-L375","documentation":"Sybase ASE has no native OFFSET/FETCH, so SybaseSqlAstTranslator-style emulations rely on window functions and TOP. visitOffsetFetchClause in SybaseASESqlAstTranslator can emulate paging in the root query, and even an offset or a fetch alone in a subquery, but not BOTH an offset and a fetch clause inside a non-root query part -- it throws IllegalArgumentException('Can't emulate offset fetch clause in subquery'). The check only applies outside the full-join-emulation helper paths and after assertRowsOnlyFetchClauseType has validated the fetch type.","triggerScenarios":"HQL that puts paging inside a subquery with both offset and fetch, e.g. 'where x.id in (select y.id from Y y order by y.k offset 10 fetch first 5 rows only)' (or the equivalent HQL limit/offset syntax in a subquery), executed on SybaseASEDialect. Also reachable through criteria subqueries carrying both page parameters. Throws at SQL rendering time.","commonSituations":"Top-N-per-group queries (id in (select ... offset ... fetch ...)) written for PostgreSQL/standard SQL and run against ASE; query porting during a Sybase migration; Hibernate 6.6+ HQL paging syntax used inside subqueries.","solutions":["Move pagination to the outermost query: use setFirstResult/setMaxResults on the root Query instead of offset/fetch inside the subquery","Rewrite the top-N-per-group pattern using ROW_NUMBER() OVER (PARTITION BY ...) <= n in a derived table (native SQL if HQL cannot express it)","Fetch the subquery ids without paging and page the result in application memory","Precompute the paged ids into a temp table via native ASE SQL and join against it"],"exampleFix":"// before (throws on Sybase ASE)\n\"select o from Order o where o.customerId in (select c.id from Customer c order by c.name offset 10 fetch first 20 rows only)\"\n\n// after: page at the root, not in the subquery\nList<Long> ids = em.createQuery(\"select c.id from Customer c order by c.name\", Long.class)\n    .setFirstResult(10).setMaxResults(20).getResultList();\nList<Order> orders = em.createQuery(\"select o from Order o where o.customerId in :ids\", Order.class)\n    .setParameter(\"ids\", ids).getResultList();","handlingStrategy":"validation","validationCode":"// Reject paging inside subqueries before executing on Sybase ASE\nstatic boolean subqueryHasPaging(String hql) {\n    // crude but effective guard for review gates; subquery = any '(' depth > 0\n    int depth = 0;\n    String u = hql.toUpperCase();\n    for (int i = 0; i < u.length(); i++) {\n        char c = u.charAt(i);\n        if (c == '(') depth++;\n        else if (c == ')') depth--;\n        else if (depth > 0 && u.startsWith(\"OFFSET\", i)) return true;\n    }\n    return false;\n}\n\nif (dialect instanceof SybaseASEDialect && subqueryHasPaging(hql)) {\n    throw new IllegalArgumentException(\"Move offset/fetch to the root query for Sybase ASE\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql, cls).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage().startsWith(\"Can't emulate offset fetch clause in subquery\")) {\n        return pagedRootAlternative(hql); // page at root or ROW_NUMBER rewrite\n    }\n    throw e;\n}","preventionTips":["Keep setFirstResult/setMaxResults at the root Query only; never hand-write offset/fetch in HQL subqueries","Prefer ROW_NUMBER()-over-derived-table patterns for top-N-per-group so they port to ASE","Review new HQL for 'offset'/'fetch' tokens inside parentheses when Sybase ASE is a target"],"tags":["hibernate","sybase-ase","pagination","offset-fetch","subquery","query-translation"],"backgroundTag":"pagination-in-subquery-not-supported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}