{"record":{"id":"befef83791803615","repo":"hibernate/hibernate-orm","slug":"can-t-emulate-offset-clause-in-subquery-befef8","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/SybaseLegacySqlAstTranslator.java","lineNumber":206,"sourceCode":"\n\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\tassertRowsOnlyFetchClauseType( queryPart );\n\t\tif ( !queryPart.isRoot() && 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":188,"sourceCodeEnd":224,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/SybaseLegacySqlAstTranslator.java#L188-L224","documentation":"SybaseLegacySqlAstTranslator (Sybase ASE) can limit rows only with TOP and has no way to emulate an OFFSET that appears in a subquery. Unlike the SQL Anywhere translator there is no version exemption: any non-root query part with an offset clause makes visitOffsetFetchClause throw IllegalArgumentException during translation.","triggerScenarios":"HQL/Criteria on SybaseLegacyDialect where a subquery or set-operation arm has an offset, e.g. `... in (select x.id from X x offset 10)` or a Criteria subquery with setFirstResult().","commonSituations":"Spring Data pagination reaching into subqueries; queries written against modern databases with OFFSET/FETCH then pointed at ASE; Hibernate 5 to 6 migrations where emulation behavior changed.","solutions":["Apply offset/setFirstResult() only to the root query and keep subqueries unpaginated","Replace the subquery offset with TOP (`select top 5 ...`) or a row-number derived table","Restructure the query as a join so no nested offset is needed","Use a native SQL query for that statement"],"exampleFix":"// before\nselect o from Order o\n where o.customerId in (select c.id from Customer c order by c.name offset 20)\n\n// after\nList<Order> rows = em.createQuery(\n    \"select o from Order o where o.customerId in (select c.id from Customer c)\", Order.class)\n    .setFirstResult(20)\n    .getResultList();","handlingStrategy":"validation","validationCode":"// On Sybase ASE, reject query building that puts offsets in subqueries\nDialect d = sessionFactory.getJdbcServices().getDialect();\nif (d instanceof SybaseLegacyDialect && hql.toLowerCase(Locale.ROOT).matches(\"(?s).*\\\\bfrom\\\\b[^)]*\\\\boffset\\\\b.*\")) {\n    throw new IllegalArgumentException(\"Subquery offset not supported on Sybase ASE\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    return em.createQuery(hql).getResultList();\n} catch (IllegalArgumentException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"offset clause in subquery\")) {\n        // rewrite without nested offset or use native SQL, then retry once\n    }\n    throw e;\n}","preventionTips":["Apply pagination exclusively at the root query on ASE","Replace subquery offsets with TOP or row-number derived tables during query review","Add a static check (or ArchUnit rule) forbidding the 'offset' keyword inside subselect fragments"],"tags":["hibernate","sybase-ase","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"}