https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35361 Bug ID: 35361 Summary: Search filter on receive orders page crashes the system Change sponsored?: --- Product: Koha Version: 23.05 Hardware: All OS: All Status: NEW Severity: critical Priority: P5 - low Component: REST API Assignee: koha-bugs@lists.koha-community.org Reporter: andreas.jonsson@kreablo.se CC: tomascohen@gmail.com The search filter for the table on the page acqui/parcel.pl generates the below api-request, which results in the below sql query. Note how the LIMIT clause is applied to the sub-query, not the whole query. For us, this specific query actually yielded a search result of in total over 171 000 rows. GET /api/v1/acquisitions/orders?only_active=1&_page=1&_per_page=20&q={"-and":[{"basket.vendor_id":5},[[{"basket.name":{"like":"%97891%"}}],[{"basket.basket_group.name":{"like":"%97891%"}}],[{"me.order_id":{"like":"%97891%"}}],[{"biblio.title":{"like":"%97891%"}},{"biblio.author":{"like":"%97891%"}},{"biblio.isbn":{"like":"%97891%"}},{"biblio.publisher":{"like":"%97891%"}},{"me.internal_note":{"like":"%97891%"}},{"me.vendor_note":{"like":"%97891%"}}],[{"me.replacement_price":{"like":"%97891%"}}],[{"me.quantity":{"like":"%97891%"}}],[{"me.ecost":{"like":"%97891%"}}],[{"fund.name":{"like":"%97891%"}}]]]}&_match=contains&_order_by=+basket.name HTTP/1.1 SELECT `me`.`ordernumber`, `me`.`biblionumber`, `me`.`deleted_biblionumber`, `me`.`entrydate`, `me`.`quantity`, `me`.`currency`, `me`.`listprice`, `me`.`datereceived`, `me`.`invoiceid`, `me`.`freight`, `me`.`unitprice`, `me`.`unitprice_tax_excluded`, `me`.`unitprice_tax_included`, `me`.`quantityreceived`, `me`.`created_by`, `me`.`datecancellationprinted`, `me`.`cancellationreason`, `me`.`order_internalnote`, `me`.`order_vendornote`, `me`.`purchaseordernumber`, `me`.`basketno`, `me`.`timestamp`, `me`.`rrp`, `me`.`replacementprice`, `me`.`rrp_tax_excluded`, `me`.`rrp_tax_included`, `me`.`ecost`, `me`.`ecost_tax_excluded`, `me`.`ecost_tax_included`, `me`.`tax_rate_bak`, `me`.`tax_rate_on_ordering`, `me`.`tax_rate_on_receiving`, `me`.`tax_value_bak`, `me`.`tax_value_on_ordering`, `me`.`tax_value_on_receiving`, `me`.`discount`, `me`.`budget_id`, `me`.`budgetdate`, `me`.`sort1`, `me`.`sort2`, `me`.`sort1_authcat`, `me`.`sort2_authcat`, `me`.`uncertainprice`, `me`.`subscriptionid`, `me`.`parent_ordernumber`, `me`.`orderstatus`, `me`.`line_item_id`, `me`.`suppliers_reference_number`, `me`.`suppliers_reference_qualifier`, `me`.`suppliers_report`, `me`.`estimated_delivery_date`, `me`.`invoice_unitprice`, `me`.`invoice_currency`, `basket`.`basketno`, `basket`.`basketname`, `basket`.`note`, `basket`.`booksellernote`, `basket`.`contractnumber`, `basket`.`creationdate`, `basket`.`closedate`, `basket`.`booksellerid`, `basket`.`authorisedby`, `basket`.`booksellerinvoicenumber`, `basket`.`basketgroupid`, `basket`.`deliveryplace`, `basket`.`billingplace`, `basket`.`branch`, `basket`.`is_standing`, `basket`.`create_items`, `basket_group`.`id`, `basket_group`.`name`, `basket_group`.`closed`, `basket_group`.`booksellerid`, `basket_group`.`deliveryplace`, `basket_group`.`freedeliveryplace`, `basket_group`.`deliverycomment`, `basket_group`.`billingplace`, `biblio`.`biblionumber`, `biblio`.`frameworkcode`, `biblio`.`author`, `biblio`.`title`, `biblio`.`medium`, `biblio`.`subtitle`, `biblio`.`part_number`, `biblio`.`part_name`, `biblio`.`unititle`, `biblio`.`notes`, `biblio`.`serial`, `biblio`.`seriestitle`, `biblio`.`copyrightdate`, `biblio`.`timestamp`, `biblio`.`datecreated`, `biblio`.`abstract`, `biblioitem`.`biblioitemnumber`, `biblioitem`.`biblionumber`, `biblioitem`.`volume`, `biblioitem`.`number`, `biblioitem`.`itemtype`, `biblioitem`.`isbn`, `biblioitem`.`issn`, `biblioitem`.`ean`, `biblioitem`.`publicationyear`, `biblioitem`.`publishercode`, `biblioitem`.`volumedate`, `biblioitem`.`volumedesc`, `biblioitem`.`collectiontitle`, `biblioitem`.`collectionissn`, `biblioitem`.`collectionvolume`, `biblioitem`.`editionstatement`, `biblioitem`.`editionresponsibility`, `biblioitem`.`timestamp`, `biblioitem`.`illus`, `biblioitem`.`pages`, `biblioitem`.`notes`, `biblioitem`.`size`, `biblioitem`.`place`, `biblioitem`.`lccn`, `biblioitem`.`url`, `biblioitem`.`cn_source`, `biblioitem`.`cn_class`, `biblioitem`.`cn_item`, `biblioitem`.`cn_suffix`, `biblioitem`.`cn_sort`, `biblioitem`.`agerestriction`, `biblioitem`.`totalissues`, `items`.`itemnumber`, `items`.`biblionumber`, `items`.`biblioitemnumber`, `items`.`barcode`, `items`.`dateaccessioned`, `items`.`booksellerid`, `items`.`homebranch`, `items`.`price`, `items`.`replacementprice`, `items`.`replacementpricedate`, `items`.`datelastborrowed`, `items`.`datelastseen`, `items`.`stack`, `items`.`notforloan`, `items`.`damaged`, `items`.`damaged_on`, `items`.`itemlost`, `items`.`itemlost_on`, `items`.`withdrawn`, `items`.`withdrawn_on`, `items`.`itemcallnumber`, `items`.`coded_location_qualifier`, `items`.`issues`, `items`.`renewals`, `items`.`reserves`, `items`.`restricted`, `items`.`itemnotes`, `items`.`itemnotes_nonpublic`, `items`.`holdingbranch`, `items`.`timestamp`, `items`.`deleted_on`, `items`.`location`, `items`.`permanent_location`, `items`.`onloan`, `items`.`cn_source`, `items`.`cn_sort`, `items`.`ccode`, `items`.`materials`, `items`.`uri`, `items`.`itype`, `items`.`more_subfields_xml`, `items`.`enumchron`, `items`.`copynumber`, `items`.`stocknumber`, `items`.`new_status`, `items`.`exclude_from_local_holds_priority`, `suggestions`.`suggestionid`, `suggestions`.`suggestedby`, `suggestions`.`suggesteddate`, `suggestions`.`managedby`, `suggestions`.`manageddate`, `suggestions`.`acceptedby`, `suggestions`.`accepteddate`, `suggestions`.`rejectedby`, `suggestions`.`rejecteddate`, `suggestions`.`lastmodificationby`, `suggestions`.`lastmodificationdate`, `suggestions`.`STATUS`, `suggestions`.`archived`, `suggestions`.`note`, `suggestions`.`staff_note`, `suggestions`.`author`, `suggestions`.`title`, `suggestions`.`copyrightdate`, `suggestions`.`publishercode`, `suggestions`.`date`, `suggestions`.`volumedesc`, `suggestions`.`publicationyear`, `suggestions`.`place`, `suggestions`.`isbn`, `suggestions`.`biblionumber`, `suggestions`.`reason`, `suggestions`.`patronreason`, `suggestions`.`budgetid`, `suggestions`.`branchcode`, `suggestions`.`collectiontitle`, `suggestions`.`itemtype`, `suggestions`.`quantity`, `suggestions`.`currency`, `suggestions`.`price`, `suggestions`.`total`, `suggester`.`borrowernumber`, `suggester`.`cardnumber`, `suggester`.`surname`, `suggester`.`firstname`, `suggester`.`middle_name`, `suggester`.`title`, `suggester`.`othernames`, `suggester`.`initials`, `suggester`.`pronouns`, `suggester`.`streetnumber`, `suggester`.`streettype`, `suggester`.`address`, `suggester`.`address2`, `suggester`.`city`, `suggester`.`state`, `suggester`.`zipcode`, `suggester`.`country`, `suggester`.`email`, `suggester`.`phone`, `suggester`.`mobile`, `suggester`.`fax`, `suggester`.`emailpro`, `suggester`.`phonepro`, `suggester`.`B_streetnumber`, `suggester`.`B_streettype`, `suggester`.`B_address`, `suggester`.`B_address2`, `suggester`.`B_city`, `suggester`.`B_state`, `suggester`.`B_zipcode`, `suggester`.`B_country`, `suggester`.`B_email`, `suggester`.`B_phone`, `suggester`.`dateofbirth`, `suggester`.`branchcode`, `suggester`.`categorycode`, `suggester`.`dateenrolled`, `suggester`.`dateexpiry`, `suggester`.`password_expiration_date`, `suggester`.`date_renewed`, `suggester`.`gonenoaddress`, `suggester`.`lost`, `suggester`.`debarred`, `suggester`.`debarredcomment`, `suggester`.`contactname`, `suggester`.`contactfirstname`, `suggester`.`contacttitle`, `suggester`.`borrowernotes`, `suggester`.`relationship`, `suggester`.`sex`, `suggester`.`password`, `suggester`.`secret`, `suggester`.`auth_method`, `suggester`.`flags`, `suggester`.`userid`, `suggester`.`opacnote`, `suggester`.`contactnote`, `suggester`.`sort1`, `suggester`.`sort2`, `suggester`.`altcontactfirstname`, `suggester`.`altcontactsurname`, `suggester`.`altcontactaddress1`, `suggester`.`altcontactaddress2`, `suggester`.`altcontactaddress3`, `suggester`.`altcontactstate`, `suggester`.`altcontactzipcode`, `suggester`.`altcontactcountry`, `suggester`.`altcontactphone`, `suggester`.`smsalertnumber`, `suggester`.`sms_provider_id`, `suggester`.`privacy`, `suggester`.`privacy_guarantor_fines`, `suggester`.`privacy_guarantor_checkouts`, `suggester`.`checkprevcheckout`, `suggester`.`updated_on`, `suggester`.`lastseen`, `suggester`.`lang`, `suggester`.`login_attempts`, `suggester`.`overdrive_auth_token`, `suggester`.`anonymized`, `suggester`.`autorenew_checkouts`, `suggester`.`primary_contact_method`, `fund`.`budget_id`, `fund`.`budget_parent_id`, `fund`.`budget_code`, `fund`.`budget_name`, `fund`.`budget_branchcode`, `fund`.`budget_amount`, `fund`.`budget_encumb`, `fund`.`budget_expend`, `fund`.`budget_notes`, `fund`.`timestamp`, `fund`.`budget_period_id`, `fund`.`sort1_authcat`, `fund`.`sort2_authcat`, `fund`.`budget_owner_id`, `fund`.`budget_permission` FROM (SELECT `me`.`ordernumber`, `me`.`biblionumber`, `me`.`deleted_biblionumber`, `me`.`entrydate`, `me`.`quantity`, `me`.`currency`, `me`.`listprice`, `me`.`datereceived`, `me`.`invoiceid`, `me`.`freight`, `me`.`unitprice`, `me`.`unitprice_tax_excluded`, `me`.`unitprice_tax_included`, `me`.`quantityreceived`, `me`.`created_by`, `me`.`datecancellationprinted`, `me`.`cancellationreason`, `me`.`order_internalnote`, `me`.`order_vendornote`, `me`.`purchaseordernumber`, `me`.`basketno`, `me`.`timestamp`, `me`.`rrp`, `me`.`replacementprice`, `me`.`rrp_tax_excluded`, `me`.`rrp_tax_included`, `me`.`ecost`, `me`.`ecost_tax_excluded`, `me`.`ecost_tax_included`, `me`.`tax_rate_bak`, `me`.`tax_rate_on_ordering`, `me`.`tax_rate_on_receiving`, `me`.`tax_value_bak`, `me`.`tax_value_on_ordering`, `me`.`tax_value_on_receiving`, `me`.`discount`, `me`.`budget_id`, `me`.`budgetdate`, `me`.`sort1`, `me`.`sort2`, `me`.`sort1_authcat`, `me`.`sort2_authcat`, `me`.`uncertainprice`, `me`.`subscriptionid`, `me`.`parent_ordernumber`, `me`.`orderstatus`, `me`.`line_item_id`, `me`.`suppliers_reference_number`, `me`.`suppliers_reference_qualifier`, `me`.`suppliers_report`, `me`.`estimated_delivery_date`, `me`.`invoice_unitprice`, `me`.`invoice_currency` FROM `aqorders` `me` LEFT JOIN `aqbasket` `basket` ON `basket`.`basketno` = `me`.`basketno` LEFT JOIN `aqbasketgroups` `basket_group` ON `basket_group`.`id` = `basket`.`basketgroupid` LEFT JOIN `biblio` `biblio` ON `biblio`.`biblionumber` = `me`.`biblionumber` LEFT JOIN `biblioitems` `biblioitem` ON `biblioitem`.`biblionumber` = `biblio`.`biblionumber` JOIN `aqbudgets` `fund` ON `fund`.`budget_id` = `me`.`budget_id` WHERE ( ( ( ( `me`.`ecost` LIKE '%97891%' OR `me`.`quantity` LIKE '%97891%' OR `me`.`ordernumber` LIKE '%97891%' OR `fund`.`budget_name` LIKE '%97891%' OR `basket`.`basketname` LIKE '%97891%' OR `basket_group`.`name` LIKE '%97891%' OR `me`.`replacementprice` LIKE '%97891%' OR `biblio`.`title` LIKE '%97891%' OR `biblio`.`author` LIKE '%97891%' OR `biblioitem`.`isbn` LIKE '%97891%' OR `me`.`order_vendornote` LIKE '%97891%' OR `me`.`order_internalnote` LIKE '%97891%' OR `biblioitem`.`publishercode` LIKE '%97891%' ) AND ( ( `orderstatus` = 'ordered' OR `orderstatus` = 'partial' ) OR ( `basket`.`is_standing` = '1' AND ( `orderstatus` = 'new' OR `orderstatus` = 'ordered' OR `orderstatus` = 'partial' ) ) ) ) AND `basket`.`booksellerid` = '5' ) ) GROUP BY `me`.`ordernumber`, `me`.`biblionumber`, `me`.`deleted_biblionumber`, `me`.`entrydate`, `me`.`quantity`, `me`.`currency`, `me`.`listprice`, `me`.`datereceived`, `me`.`invoiceid`, `me`.`freight`, `me`.`unitprice`, `me`.`unitprice_tax_excluded`, `me`.`unitprice_tax_included`, `me`.`quantityreceived`, `me`.`created_by`, `me`.`datecancellationprinted`, `me`.`cancellationreason`, `me`.`order_internalnote`, `me`.`order_vendornote`, `me`.`purchaseordernumber`, `me`.`basketno`, `me`.`timestamp`, `me`.`rrp`, `me`.`replacementprice`, `me`.`rrp_tax_excluded`, `me`.`rrp_tax_included`, `me`.`ecost`, `me`.`ecost_tax_excluded`, `me`.`ecost_tax_included`, `me`.`tax_rate_bak`, `me`.`tax_rate_on_ordering`, `me`.`tax_rate_on_receiving`, `me`.`tax_value_bak`, `me`.`tax_value_on_ordering`, `me`.`tax_value_on_receiving`, `me`.`discount`, `me`.`budget_id`, `me`.`budgetdate`, `me`.`sort1`, `me`.`sort2`, `me`.`sort1_authcat`, `me`.`sort2_authcat`, `me`.`uncertainprice`, `me`.`subscriptionid`, `me`.`parent_ordernumber`, `me`.`orderstatus`, `me`.`line_item_id`, `me`.`suppliers_reference_number`, `me`.`suppliers_reference_qualifier`, `me`.`suppliers_report`, `me`.`estimated_delivery_date`, `me`.`invoice_unitprice`, `me`.`invoice_currency`, `basket`.`basketname` ORDER BY `basket`.`basketname` ASC LIMIT 20) `me` LEFT JOIN `aqbasket` `basket` ON `basket`.`basketno` = `me`.`basketno` LEFT JOIN `aqbasketgroups` `basket_group` ON `basket_group`.`id` = `basket`.`basketgroupid` LEFT JOIN `biblio` `biblio` ON `biblio`.`biblionumber` = `me`.`biblionumber` LEFT JOIN `biblioitems` `biblioitem` ON `biblioitem`.`biblionumber` = `biblio`.`biblionumber` LEFT JOIN `items` `items` ON `items`.`biblionumber` = `biblio`.`biblionumber` LEFT JOIN `suggestions` `suggestions` ON `suggestions`.`biblionumber` = `biblio`.`biblionumber` LEFT JOIN `borrowers` `suggester` ON `suggester`.`borrowernumber` = `suggestions`.`suggestedby` JOIN `aqbudgets` `fund` ON `fund`.`budget_id` = `me`.`budget_id` WHERE ( ( ( ( `me`.`ecost` LIKE '%97891%' OR `me`.`quantity` LIKE '%97891%' OR `me`.`ordernumber` LIKE '%97891%' OR `fund`.`budget_name` LIKE '%97891%' OR `basket`.`basketname` LIKE '%97891%' OR `basket_group`.`name` LIKE '%97891%' OR `me`.`replacementprice` LIKE '%97891%' OR `biblio`.`title` LIKE '%97891%' OR `biblio`.`author` LIKE '%97891%' OR `biblioitem`.`isbn` LIKE '%97891%' OR `me`.`order_vendornote` LIKE '%97891%' OR `me`.`order_internalnote` LIKE '%97891%' OR `biblioitem`.`publishercode` LIKE '%97891%' ) AND ( ( `orderstatus` = 'ordered' OR `orderstatus` = 'partial' ) OR ( `basket`.`is_standing` = '1' AND ( `orderstatus` = 'new' OR `orderstatus` = 'ordered' OR `orderstatus` = 'partial' ) ) ) ) AND `basket`.`booksellerid` = '5' ) ) ORDER BY `basket`.`basketname` ASC; -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.