Bug 35966 - Koha should not strip limits from SQL queries
Summary: Koha should not strip limits from SQL queries
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2024-01-31 19:33 UTC by Andrew Fuerste-Henry
Modified: 2024-02-05 17:54 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Andrew Fuerste-Henry 2024-01-31 19:33:38 UTC
Koha removes LIMIT from queries in some cases, which can cause incorrect results. For example, consider this report to give the 5 oldest items in the collection, sorted into callnumber order:
SELECT *
FROM (
SELECT itemnumber, cn_sort, dateaccessioned
FROM items
ORDER BY dateaccessioned
LIMIT 5
) foo
ORDER BY cn_sort

That query should get all the items, put them in dateaccessioned order, drop all but the first 5, and then put those 5 in cn_sort order.

But Koha processes that by dropping the LIMIT and then re-applying it at the end:
SELECT *
FROM (
SELECT itemnumber, cn_sort, dateaccessioned
FROM items
ORDER BY dateaccessioned
) foo
ORDER BY cn_sort
LIMIT 5

That makes the query get all the items, put them in dateaccessioned order, then put them in cn_sort order, then drop all but the first 5. It changes the query hugely. 

It only does this in queries without a WHERE, so that first query can be made to work by editing it to: 

SELECT *
FROM (
SELECT itemnumber, cn_sort, dateaccessioned
FROM items
ORDER BY dateaccessioned
LIMIT 5
) foo
WHERE 1=1
ORDER BY cn_sort