https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35966
Bug ID: 35966
Summary: Koha should not strip limits from SQL queries
Change sponsored?: ---
Product: Koha
Version: master
Hardware: All
OS: All
Status: NEW
Severity: normal
Priority: P5 - low
Component: Reports
Assignee: koha-bugs@lists.koha-community.org
Reporter: andre...@dubcolib.org
QA Contact: testo...@bugs.koha-community.org
CC: n...@bywatersolutions.com
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
--
You are receiving this mail because:
You are the assignee for the bug.
You are watching all bug changes.
_______________________________________________
Koha-bugs mailing list
Koha-bugs@lists.koha-community.org
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
