[Bug 21725] New: Incorrect HAVING in group by in Acquisitions.pm
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Bug ID: 21725 Summary: Incorrect HAVING in group by in Acquisitions.pm Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Acquisitions Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org prove -v t/db_dependent/Acquisition/GetBasketsInfosByBookseller.t DBD::mysql::st execute failed: Non-grouping field 'expected_items' is used in HAVING clause [for Statement " SELECT aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items, SUM(aqorders.quantity) AS total_items, SUM( IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 ) ) AS total_items_cancelled, COUNT(DISTINCT aqorders.biblionumber) AS total_biblios, SUM( IF(aqorders.datereceived IS NULL AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') , aqorders.quantity , 0) ) AS expected_items FROM aqbasket LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno WHERE booksellerid = ? GROUP BY aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items HAVING (closedate IS NULL OR (expected_items > 0))" with ParamValues: 0='359'] at /home/vagrant/kohaclone/C4/Acquisition.pm line 719. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 --- Comment #1 from Nick Clemens <nick@bywatersolutions.com> --- Created attachment 81616 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=81616&action=edit Bug 21725: Use full statement in HAVING rather than constructed column To test: 1 - prove -v t/db_dpendent/Acquisition/GetBasketsInfosByBookseller.t 2 - Apply patch 3 - prove -v t/db_dpendent/Acquisition/GetBasketsInfosByBookseller.t 4 - It passes 5 - Have some open/closed and some fully and not full received baskets 6 - View a vendor and confirm baskets are shwn as expected 7 - Check 'Show all baskets' shows all baskets -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | CC| |jonathan.druart@bugs.koha-c | |ommunity.org, | |nick@bywatersolutions.com Blocks| |17258 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17258 [Bug 17258] [OMNIBUS] MySQL 5.7 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew.isherwood@ptfs-europ | |e.com --- Comment #2 from Nick Clemens <nick@bywatersolutions.com> --- *** Bug 21714 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #81616|0 |1 is obsolete| | --- Comment #3 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Created attachment 81626 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=81626&action=edit Bug 21725: Use full statement in HAVING rather than constructed column To test: 1 - prove -v t/db_dpendent/Acquisition/GetBasketsInfosByBookseller.t 2 - Apply patch 3 - prove -v t/db_dpendent/Acquisition/GetBasketsInfosByBookseller.t 4 - It passes 5 - Have some open/closed and some fully and not full received baskets 6 - View a vendor and confirm baskets are shwn as expected 7 - Check 'Show all baskets' shows all baskets Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off CC| |martin.renvoize@ptfs-europe | |.com --- Comment #4 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Works as expected and I'm happy there are no regressions (yay for tests!) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- As I said on bug 21714 I do not recreate the issue. That would be good to understand why. The query is (I removed the WHERE clause): SELECT aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items, SUM(aqorders.quantity) AS total_items, SUM( IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 ) ) AS total_items_cancelled, COUNT(DISTINCT aqorders.biblionumber) AS total_biblios, SUM( IF(aqorders.datereceived IS NULL AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') , aqorders.quantity , 0) ) AS expected_items FROM aqbasket LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno GROUP BY aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items HAVING (closedate IS NULL OR (expected_items > 0)); Can you confirm it fail for you after having set the modes accordingly? SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Server version: 10.0.36-MariaDB-0+deb8u1 (Debian) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 --- Comment #6 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- I wanted to move the condition to the WHERE clause. The advantage to have it into the HAVING clause was to not repeat the condition. With this patch we c/p the condition and have it in the HAVING clause... :) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 --- Comment #7 from Nick Clemens <nick@bywatersolutions.com> --- (In reply to Jonathan Druart from comment #6)
I wanted to move the condition to the WHERE clause. The advantage to have it into the HAVING clause was to not repeat the condition. With this patch we c/p the condition and have it in the HAVING clause... :)
I tried moving things to the where, but this removed orders from the total_orders count so I went this route. I can confirm setting the modes as you explain and then running the query fails for me However: Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1 Possibly: https://bugs.mysql.com/bug.php?id=51058 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 --- Comment #8 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Indeed.. I couldn't work out how to move it to the WHERE clause.. I tried pretty hard to during work on but 21385. I agree, this is the MySQL bug highlighted by Nick. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #81626|0 |1 is obsolete| | --- Comment #9 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Created attachment 81812 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=81812&action=edit Bug 21725: Use full statement in HAVING rather than constructed column To test: 1 - prove -v t/db_dpendent/Acquisition/GetBasketsInfosByBookseller.t 2 - Apply patch 3 - prove -v t/db_dpendent/Acquisition/GetBasketsInfosByBookseller.t 4 - It passes 5 - Have some open/closed and some fully and not full received baskets 6 - View a vendor and confirm baskets are shwn as expected 7 - Check 'Show all baskets' shows all baskets Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> Note sure this is the best thing but it fixes the issue -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Nick Clemens <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to Master --- Comment #10 from Nick Clemens <nick@bywatersolutions.com> --- Awesome work all! Pushed to master for 18.11 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to Master |Pushed to Stable --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Pushed to 18.05.x for 18.05.06 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Fridolin SOMERS <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m Resolution|--- |FIXED Status|Pushed to Stable |RESOLVED --- Comment #12 from Fridolin SOMERS <fridolin.somers@biblibre.com> --- SQL modes not in 17.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21725 Andrew Isherwood <bugzilla@warmlight.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC|bugzilla@warmlight.co.uk | -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org