We're updating the issue view to help you get more done. 

[Regression] SQLServerDialect no longer supports setFirstResult(n)

Description

As of 4.x Hibernate would allow queries to specify offset even if the underlying database had no way to support offset. How it accomplished that is a very suboptimal (from a perf spective) solution in which all rows up to the requested limit are returned in the JDBC ResultSet and then in memory we would advance the ResultSet to the requested offset.

SQL Server 2000 (SQLServerDialect) is one such database.

In 5.x we changed this to instead throw an exception, thereby circumventing the poor performance. In the interest of users upgrading, we should allow them to re-enable the older behavior. To this end we will add a compatibility setting (defaulting to false) that allows such users to re-enable the legacy behavior.

NOTE : that we have zero plans to "improve" the way this is handled - just allow users to opt-in to the older behavior. At some point we will consider removing this setting as well. No modern versions of these DBs we support (that we know of) lack support for limit/offset.

As a work-around you could certainly supply a custom Dialect for the purpose of returning a "better" LimitHandler - specifically one that defines LimitHandler#supportsLimitOffset as false for SQL Server 2000. That should give you the same behavior.


Original descripton


In a legacy project I have to connect to a MS SQL Server 2000. This worked well with the latest 4.x Hibernate using SQLServerDialect & jtds JDBC Driver.

Test-Case: https://github.com/ElderByte-/hibernate-setFirstResult-mssql

However, now I upgraded from Hibernate 4.x to Hibernate 5.x and pagination (EntityManager.setFirstResult() -> query.setFirstResult()) is no longer supported. Hibernate now throws the exception below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 [2016-10-21T18:34:23.732075193Z] java.lang.UnsupportedOperationException: query result offset is not supported [2016-10-21T18:34:23.732141234Z] at org.hibernate.dialect.pagination.TopLimitHandler.processSql(TopLimitHandler.java:49) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732186954Z] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1904) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732271373Z] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732283992Z] at org.hibernate.loader.Loader.doQuery(Loader.java:932) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732373101Z] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732385606Z] at org.hibernate.loader.Loader.doList(Loader.java:2615) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732428326Z] at org.hibernate.loader.Loader.doList(Loader.java:2598) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732485046Z] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732496084Z] at org.hibernate.loader.Loader.list(Loader.java:2425) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732585796Z] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732596992Z] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732641665Z] at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732711384Z] at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1460) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732728878Z] at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1426) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732786867Z] at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.73283995Z] at org.hibernate.Query.getResultList(Query.java:417) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732853434Z] at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:72) ~[hibernate-core-5.2.3.Final.jar!/:5.2.3.Final] [2016-10-21T18:34:23.732892907Z] at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:586) ~[spring-data-jpa-1.10.3.RELEASE.jar!/:na] [2016-10-21T18:34:23.732933276Z] at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:478) ~[spring-data-jpa-1.10.3.RELEASE.jar!/:na]

Environment

Hibernate 5.2.3, MS SQLServer 2000, net.sourceforge.jtds.jdbc.Driver 1.3.1

Status

Assignee

Chris Cranford

Reporter

PascalB

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.3

Priority

Major