{"record":{"id":"0150f21340012458","repo":"hibernate/hibernate-orm","slug":"paged-queries-not-supported-by","errorCode":null,"errorMessage":"Paged queries not supported by {}","messagePattern":"Paged queries not supported by (.+?)","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-core/src/main/java/org/hibernate/dialect/pagination/AbstractLimitHandler.java","lineNumber":140,"sourceCode":"\t * Does this dialect require a one-based offset to be specified in the offset clause?\n\t *\n\t * @implNote The value passed into {@link AbstractLimitHandler#processSql(String, Limit)}\n\t *           has a zero-based offset. Handlers which do not {@link #supportsVariableLimit}\n\t *           should take care to perform any needed first-row-conversion calls prior to\n\t *           injecting the limit values into the SQL string.\n\t *\n\t * @param zeroBasedFirstResult The user-supplied, zero-based first row offset.\n\t *\n\t * @return The resulting offset, adjusted to one-based if necessary.\n\t */\n\tpublic int convertToFirstRowValue(int zeroBasedFirstResult) {\n\t\treturn zeroBasedFirstResult;\n\t}\n\n\n\t@Override\n\tpublic String processSql(String sql, Limit limit) {\n\t\tthrow new UnsupportedOperationException( \"Paged queries not supported by \" + getClass().getName() );\n\t}\n\n\t@Override\n\tpublic int bindLimitParametersAtStartOfQuery(Limit limit, PreparedStatement statement, int index)\n\t\t\tthrows SQLException {\n\t\treturn bindLimitParametersFirst()\n\t\t\t\t? bindLimitParameters( limit, statement, index )\n\t\t\t\t: 0;\n\t}\n\n\t@Override\n\tpublic int bindLimitParametersAtEndOfQuery(Limit limit, PreparedStatement statement, int index)\n\t\t\tthrows SQLException {\n\t\treturn !bindLimitParametersFirst()\n\t\t\t\t? bindLimitParameters( limit, statement, index )\n\t\t\t\t: 0;\n\t}\n","sourceCodeStart":122,"sourceCodeEnd":158,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-core/src/main/java/org/hibernate/dialect/pagination/AbstractLimitHandler.java#L122-L158","documentation":"When a query carries paging (setMaxResults/setFirstResult), Hibernate asks the dialect's LimitHandler to rewrite the SQL via processSql; AbstractLimitHandler's default implementation throws UnsupportedOperationException naming the handler class because the dialect cannot express LIMIT/OFFSET. Handlers that never override processSql report supportsLimit()==false (the base default) - e.g. AbstractLimitHandler.NO_LIMIT used by legacy Derby - so requesting a page on such a dialect always fails. The base Dialect.getLimitHandler() itself throws a sibling error ('this dialect does not support query pagination').","triggerScenarios":"query.setMaxResults(n) and/or setFirstResult(n) (including Spring Data PageRequest / Pageable repositories) on a dialect whose LimitHandler is AbstractLimitHandler.NO_LIMIT (DerbyLegacyDialect) or a custom handler extending AbstractLimitHandler without overriding processSql; executing the same paging query that worked on PostgreSQL against such a database.","commonSituations":"Applications migrated off deprecated Derby dialects onto DerbyLegacyDialect; custom/legacy dialects for niche databases with no LIMIT syntax; integration tests running against embedded databases lacking pagination; shared repository code assuming universal paging support.","solutions":["Remove setMaxResults/setFirstResult for that database and page in memory (fetch then subList) or with a windowed/native query the database supports","Use a database/dialect combination with real pagination support for paged views","If you own the dialect, override processSql (e.g. extend AbstractSimpleLimitHandler or OffsetFetchLimitHandler) to emit the database's limit syntax","Guard the UI/API layer: detect unsupported paging early via dialect.getLimitHandler().supportsLimit() instead of failing at query execution"],"exampleFix":"// before (throws on dialects whose LimitHandler cannot rewrite SQL)\nList<Order> page = em.createQuery(\"select o from Order o\", Order.class)\n        .setFirstResult(page * size).setMaxResults(size).getResultList();\n\n// after: page in memory when the dialect cannot paginate\nList<Order> all = em.createQuery(\"select o from Order o\", Order.class).getResultList();\nList<Order> page = all.subList(page * size, Math.min((page + 1) * size, all.size()));","handlingStrategy":"fallback","validationCode":"Dialect dialect = sessionFactory.getJdbcServices().getDialect();\nboolean canPage;\ntry {\n    canPage = dialect.getLimitHandler().supportsLimit();\n} catch (UnsupportedOperationException e) {\n    canPage = false; // dialect has no limit handler at all\n}\nif (!canPage) { /* page in memory or fail early with a clear message */ }","typeGuard":"static boolean supportsPaging(SessionFactory sf) {\n    try {\n        return sf.getJdbcServices().getDialect().getLimitHandler().supportsLimit();\n    } catch (UnsupportedOperationException e) {\n        return false;\n    }\n}","tryCatchPattern":"try {\n    return query.setFirstResult(offset).setMaxResults(size).getResultList();\n} catch (UnsupportedOperationException e) {\n    if (e.getMessage() != null && e.getMessage().startsWith(\"Paged queries not supported\")) {\n        // fall back to in-memory paging or reject paging for this datasource\n    }\n    throw e;\n}","preventionTips":["Verify the target dialect supports LIMIT/OFFSET before enabling paged endpoints","Centralize paging behind a service that checks dialect.getLimitHandler().supportsLimit()","For niche databases, override processSql in a custom LimitHandler with the native limit syntax","Run paging integration tests against every supported database"],"tags":["pagination","limit-handler","dialect","query","hibernate"],"backgroundTag":"query-pagination-unsupported","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}