The use of LIMIT and OFFSET in a query with MS SQL Server fails

Description

Offset and limit in a query fails with an SQL exception, f the underlying db is MS SQL Server.

I attach a (dummy) add-on that replicates the SQL exception thrown by the AO library when the database is MS SQL Server and there’s a query with LIMIT and/or OFFSET.

Go to the “Replicator!” button in the top left navigation bar (or access to http://yourURL/plugins/servlet/share/admin/linksoverview) and you will get the

java.sql.SQLException: ResultSet may only be accessed in a forward direction.

If not, reload again.

The troublesome code is in the “LinksDataAOServiceImpl” class:

Query.select(queryFields).limit(pageSize).offset(startIndex)

Which is called in the “DummyData” class:

List<LinksDataEntity> LinksDataEntityList = linksDataAOService.loadAllInRange(10, 0);

Environment

MS SQL Server 12.00.2269
<confluence.version>5.7.4</confluence.version>
<confluence.data.version>5.7.1</confluence.data.version>
<amps.version>5.1.11</amps.version>
<plugin.testrunner.version>1.2.3</plugin.testrunner.version>
<ao.version>0.28.12</ao.version>
<atlassian.templaterenderer.version>1.5.4</atlassian.templaterenderer.version>

Testing Notes

Add notes...

Assignee

Alex Courtis

Reporter

Gorka Puente

Add-on Type

Server

Team

None

CC

None

Risk factor

None

QA Kickoff Status

None

QA Demo Status

None

Story Points

1

Sprint

None

Fix versions

Affects versions

Priority

Major
Configure