[Koha-bugs] [Bug 35361] New: Search filter on receive orders page crashes the system

[bugzilla-daemon at bugs.koha-community.org]

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 at lists.koha-community.org
          Reporter: andreas.jonsson at kreablo.se
                CC: tomascohen at 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.