{"record":{"id":"068ecc14875ea840","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-068ecc","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-core/src/main/java/org/hibernate/dialect/sql/ast/SybaseSqlAstTranslator.java","lineNumber":224,"sourceCode":"\t@Override\n\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.getOffsetClauseExpression() != null ) {\n\t\t\t\tthrow new IllegalArgumentException( \"Can't emulate offset clause in subquery\" );\n\t\t\t}\n\t\t}\n\t}\n\n\t@Override\n\tpublic void visitQuerySpec(QuerySpec querySpec) {\n\t\tfinal var helper = currentFullJoinEmulationHelper();\n\t\tfinal boolean needsNestedHelper =\n\t\t\t\thelper.hasActiveFullJoinEmulation()\n\t\t\t\t\t\t&& !helper.isFullJoinEmulationQueryPart( querySpec );\n\t\tif ( needsNestedHelper ) {\n\t\t\tfullJoinEmulations.push( new FullJoinEmulation( this ) );\n\t\t}\n\t\ttry {\n\t\t\tfinal var currentHelper = currentFullJoinEmulationHelper();\n\t\t\tif ( !currentHelper.renderFullJoinEmulationBranchIfNeeded( querySpec, super::visitQuerySpec )\n\t\t\t\t\t&& !currentHelper.emulateFullJoinWithUnionIfNeeded( querySpec ) ) {\n\t\t\t\tsuper.visitQuerySpec( querySpec );","sourceCodeStart":206,"sourceCodeEnd":242,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/sql/ast/SybaseSqlAstTranslator.java#L206-L242","documentation":"SybaseSqlAstTranslator.visitOffsetFetchClause throws IllegalArgumentException('Can't emulate offset clause in subquery') whenever a NON-ROOT query part carries an offset clause -- even without a fetch -- because the SQL Anywhere rendering cannot emulate OFFSET inside a subquery. The check runs only outside full-join emulation query parts and after assertRowsOnlyFetchClauseType.","triggerScenarios":"HQL with an offset inside a subquery on SQLAnywhereDialect, e.g. 'where id in (select x.id from X x order by x.k offset 5)' or criteria subqueries with setFirstResult-equivalent offsets; throws at SQL rendering time.","commonSituations":"Top-N/skip-N subquery patterns ported from other databases; Hibernate 6.6+ HQL 'offset' syntax used in subqueries; pagination logic pushed into subqueries during query refactoring.","solutions":["Apply the offset at the root query level (setFirstResult on the Query) instead of inside the subquery","Rewrite the skip-N subquery using ROW_NUMBER() in a derived table (native SQL if needed)","Remove the offset from the subquery and slice the result list in memory","Use a native SQL Anywhere query with TOP/START AT semantics"],"exampleFix":"// before (throws on SQL Anywhere)\n\"select p from Post p where p.authorId in (select a.id from Author a order by a.name offset 5)\"\n\n// after: offset moved to the root query\nList<Post> posts = em.createQuery(\"select p from Post p where p.authorId in (select a.id from Author a)\", Post.class)\n    .setFirstResult(5).getResultList();","handlingStrategy":"validation","validationCode":"// SQL Anywhere: any offset in a non-root query part is untranslatable -- guard first\nstatic boolean hqlSubqueryContainsOffset(String hql) {\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 SQLAnywhereDialect && hqlSubqueryContainsOffset(hql)) {\n    throw new IllegalArgumentException(\"Move the offset to the root query for SQL Anywhere\");\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 clause in subquery\")) {\n        return rootPagedVariant(hql); // offset applied via setFirstResult on the outer query\n    }\n    throw e;\n}","preventionTips":["Apply paging only through Query.setFirstResult/setMaxResults on the root query","Avoid hand-written 'offset'/'fetch'/'limit' tokens inside HQL subqueries","For skip-N subquery patterns, prefer ROW_NUMBER() derived tables that port to SQL Anywhere"],"tags":["hibernate","sql-anywhere","sybase","pagination","offset","subquery"],"backgroundTag":"pagination-in-subquery-not-supported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}