Bug 28029 - Add ability to use OFFSET in SQL reports
Summary: Add ability to use OFFSET in SQL reports
Status: CLOSED INVALID
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low trivial
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-03-23 18:53 UTC by Joe Sikowitz
Modified: 2024-07-04 20:37 UTC (History)
1 user (show)

See Also:
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:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.