{"record":{"id":"a8e6b6a59426801e","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-a8e6b6","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/SybaseAnywhereSqlAstTranslator.java","lineNumber":174,"sourceCode":"\n\t@Override\n\tprotected void renderTopClause(QuerySpec querySpec, boolean addOffset, boolean needsParenthesis) {\n\t\tassertRowsOnlyFetchClauseType( querySpec );\n\t\tsuper.renderTopClause( querySpec, addOffset, needsParenthesis );\n\t}\n\n\t@Override\n\tprotected void renderTopStartAtClause(QuerySpec querySpec) {\n\t\tassertRowsOnlyFetchClauseType( querySpec );\n\t\tsuper.renderTopStartAtClause( querySpec );\n\t}\n\n\t@Override\n\tpublic void visitOffsetFetchClause(QueryPart queryPart) {\n\t\t// Sybase Anywhere only supports the TOP clause\n\t\tif ( getDialect().getVersion().isBefore( 9 ) && !queryPart.isRoot()\n\t\t\t\t&& useOffsetFetchClause( queryPart ) && 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":156,"sourceCodeEnd":192,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseAnywhereSqlAstTranslator.java#L156-L192","documentation":"Hibernate's SQL Anywhere translator can emulate row limiting only through the TOP clause. For dialect versions before 9, a non-root query part (a subquery or a set-operation arm) that declares an OFFSET cannot be rendered at all, so visitOffsetFetchClause throws IllegalArgumentException during SQL translation rather than producing wrong results. The guard fires when the dialect version isBefore(9), the query part is not root, useOffsetFetchClause is true for it, and an offset expression is present.","triggerScenarios":"Running HQL/Criteria on SybaseAnywhereDialect with version < 9 where a subquery carries an offset, e.g. `where e.id in (select x.id from X x order by x.id offset 10)`, a Criteria subquery with setFirstResult(), or a UNION arm that has an offset clause.","commonSituations":"Legacy SQL Anywhere 8/9 installations or a dialect built with an old explicit DatabaseVersion; Spring Data/Pageable pagination that gets pushed into subqueries; queries migrated from databases with native OFFSET/FETCH.","solutions":["Upgrade SQL Anywhere and configure the dialect with version 9+ so offsets render natively","Apply setFirstResult()/offset only to the outermost query and leave subqueries unpaginated","Rewrite the subquery to use TOP-based limiting or a row-number derived table join","Fall back to a native SQL query for the paginated statement"],"exampleFix":"// before\nselect e from Employee e\nwhere e.id in (select a.empId from Audit a order by a.at offset 5)\n\n// after: offset only on the root query\nList<Employee> rows = em.createQuery(\n    \"select e from Employee e where e.id in (select a.empId from Audit a)\", Employee.class)\n    .setFirstResult(5)\n    .getResultList();","handlingStrategy":"validation","validationCode":"// Before building the query: on SQL Anywhere < 9 keep offsets off subqueries\nDialect d = sessionFactory.getJdbcServices().getDialect();\nif (d instanceof SybaseAnywhereDialect sad && sad.getVersion().isBefore(9)) {\n    // generate HQL without 'offset' inside subqueries;\n    // apply setFirstResult() only on the root query\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).setFirstResult(off).setMaxResults(lim).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"offset clause in subquery\")) {\n        // restructure: move offset to the root query or switch to native SQL, then retry once\n        throw new UnsupportedOperationException(\"Rewrite needed: subquery offset\", e);\n    }\n    throw e;\n}","preventionTips":["Never push setFirstResult()/offset into subqueries; paginate only the outermost query","Assert the resolved dialect version at startup so old SQL Anywhere versions fail fast with a clear message","Keep a dialect capability matrix in the test suite: run pagination tests against every supported backend"],"tags":["hibernate","sql-anywhere","pagination","offset","subquery"],"backgroundTag":"offset-in-subquery-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}