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: andrewfh@dubcolib.org QA Contact: testopia@bugs.koha-community.org CC: nick@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.