https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37389 Bug ID: 37389 Summary: ILL requests table hangs when searching Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: ILL Assignee: pedro.amorim@ptfs-europe.com Reporter: pedro.amorim@ptfs-europe.com QA Contact: testopia@bugs.koha-community.org CC: liz@bywatersolutions.com, martin.renvoize@ptfs-europe.com, pedro.amorim@ptfs-europe.com, tomascohen@gmail.com Depends on: 30645 (In reply to Tomás Cohen Arazi from comment #38)
I'm not sure if this is a problem with the ILL requests DT only, but I have:
SELECT COUNT(*) FROM illrequests; 54 SELECT COUNT(*) FROM illrequestattributes; 754
That'd be -roughly- around 13/14 different attributes per request.
Using the DT filter box for searching "fra" is generating this query:
```sql SELECT COUNT( * ) FROM (SELECT `me`.`illrequest_id` FROM `illrequests` `me` LEFT JOIN `illrequestattributes` `extended_attributes` ON `extended_attributes`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_2` ON `extended_attributes_2`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_3` ON `extended_attributes_3`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_4` ON `extended_attributes_4`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_5` ON `extended_attributes_5`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_6` ON `extended_attributes_6`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_7` ON `extended_attributes_7`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illrequestattributes` `extended_attributes_8` ON `extended_attributes_8`.`illrequest_id` = `me`.`illrequest_id` LEFT JOIN `illbatches` `ill_batch` ON `ill_batch`.`ill_batch_id` = `me`.`batch_id` JOIN `branches` `library` ON `library`.`branchcode` = `me`.`branchcode` LEFT JOIN `borrowers` `patron` ON `patron`.`borrowernumber` = `me`.`borrowernumber` WHERE ( ( ( `me`.`cost` LIKE '%fra%' OR `me`.`placed` LIKE '%fra%' OR `me`.`status` LIKE '%fra%' OR `me`.`backend` LIKE '%fra%' OR `me`.`orderid` LIKE '%fra%' OR `me`.`replied` LIKE '%fra%' OR `me`.`updated` LIKE '%fra%' OR `me`.`accessurl` LIKE '%fra%' OR `me`.`biblio_id` LIKE '%fra%' OR `me`.`completed` LIKE '%fra%' OR `me`.`notesopac` LIKE '%fra%' OR `me`.`notesstaff` LIKE '%fra%' OR `me`.`price_paid` LIKE '%fra%' OR `ill_batch`.`name` LIKE '%fra%' OR `me`.`illrequest_id` LIKE '%fra%' OR `library`.`branchname` LIKE '%fra%' OR ( `extended_attributes_8`.`type` = 'type' AND `extended_attributes_8`.`value` LIKE '%fra%' ) OR ( `extended_attributes_6`.`type` = 'year' AND `extended_attributes_6`.`value` LIKE '%fra%' ) OR ( `extended_attributes_4`.`type` = 'issue' AND `extended_attributes_4`.`value` LIKE '%fra%' ) OR ( `extended_attributes_7`.`type` = 'pages' AND `extended_attributes_7`.`value` LIKE '%fra%' ) OR ( `extended_attributes_2`.`type` = 'title' AND `extended_attributes_2`.`value` LIKE '%fra%' ) OR ( `extended_attributes`.`type` = 'author' AND `extended_attributes`.`value` LIKE '%fra%' ) OR ( `extended_attributes_5`.`type` = 'volume' AND `extended_attributes_5`.`value` LIKE '%fra%' ) OR ( `extended_attributes_3`.`type` = 'article_title' AND `extended_attributes_3`.`value` LIKE '%fra%' ) OR `patron`.`surname` LIKE '%fra%' OR `patron`.`firstname` LIKE '%fra%' OR `patron`.`cardnumber` LIKE '%fra%' ) AND `status` NOT IN ( 'COMP' ) AND ( `status_alias` NOT IN ( 'COMP' ) OR `status_alias` IS NULL ) ) ) GROUP BY `me`.`illrequest_id`) `me`; ```
which kills MariaDB \o/
I can reproduce. It's bad. Taking a look. Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30645 [Bug 30645] Generated DBIC query incorrect for API searches across joined extended attributes when several terms are passed -- You are receiving this mail because: You are watching all bug changes.