Bug 28029

Summary: Add ability to use OFFSET in SQL reports
Product: Koha Reporter: Joe Sikowitz <joe>
Component: ReportsAssignee: Bugs List <koha-bugs>
Status: CLOSED INVALID QA Contact: Testopia <testopia>
Severity: trivial    
Priority: P5 - low CC: wizzyrea
Version: Main   
Hardware: All   
OS: All   
GIT URL: Initiative type: ---
Sponsorship status: --- Comma delimited list of Sponsors:
Crowdfunding goal: 0 Crowdfunding committed: 0
Crowdfunding contact: Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:

Description Joe Sikowitz 2021-03-23 18:53:19 UTC
As far as I can tell, you can't use offset [# to offset] or the abbreviated limit [# to offset], [# to limit] shorthand to offset your results in Koha's SQL reports. These features are available in MySQL and Mariadb, so it would be nice if you could use one of them on the front end.

Here are two simple examples that don't work:

select borrowernumber
from borrowers
limit 10, 10

select borrowernumber
from borrowers
limit 10 offset 10
Comment 1 Liz Rea 2023-03-08 21:45:27 UTC
Noting this feature is available as of mariadb 10.6.
Comment 2 Katrin Fischer 2023-04-16 12:46:26 UTC
It looks like the MariaDB version on ktd is too old to test this:
10.5.19-MariaDB-1:10.5.19+maria~ubu2004

The error I get indicates that the SQL statement was used as I entered it, but that the DBMS doesn't support it. So I think a change in Koha might not be needed:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10 

Joe, can you verify you were testing with 10.6 or later?

MariaDB:
https://mariadb.com/kb/en/select-offset-fetch/

MySQL seems to have had it for a while:
https://dev.mysql.com/doc/refman/8.0/en/select.html
https://dev.mysql.com/doc/refman/5.7/en/select.html
Comment 3 Katrin Fischer 2023-07-01 06:59:57 UTC
I believe this was a DBMS version related problem.