From 8808be900af7f835b1c56d473354b97a6c636c8c Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 6 Feb 2018 11:54:18 -0300 Subject: [PATCH] Bug 20144: [sql_modes] Fix GROUP BY clause in GetBasketsInfosByBookseller This need to be tested from the interface! Fix for: 'koha_kohadev.aqbasket.basketname' isn't in GROUP BY t/db_dependent/Acquisition/GetBasketsInfosByBookseller.t We need this group by. We should not need to list all fields, from mysql 5.7 doc: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html "The query is valid if name is a primary key of t or is a unique NOT NULL column. In such cases, MySQL recognizes that the selected column is functionally dependent on a grouping column. " However, MariaDB did not implemented yet: https://jira.mariadb.org/browse/MDEV-11588 Signed-off-by: Josef Moravec Signed-off-by: Julian Maurice --- C4/Acquisition.pm | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 96d9ae5ae1..1973ab90e5 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -692,7 +692,7 @@ sub GetBasketsInfosByBookseller { my $dbh = C4::Context->dbh; my $query = q{ - SELECT aqbasket.*, + 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 ) @@ -711,7 +711,7 @@ sub GetBasketsInfosByBookseller { unless ( $allbaskets ) { $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))"; } - $query.=" GROUP BY aqbasket.basketno"; + $query.=" 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"; my $sth = $dbh->prepare($query); $sth->execute($supplierid); -- 2.14.2