From c1e82d0e15cbbb3dd23260547f3b914310a96a8f Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 5 Feb 2018 17:38:32 -0300 Subject: [PATCH] Bug 20144: [sql_modes] Remove HAVING clause in GetLateOrders More attention needed here! Fix for: Non-grouping field 'quantity' is used in HAVING clause Having is useless, and certainly slower t/db_dependent/Acquisition.t Signed-off-by: Josef Moravec Signed-off-by: Julian Maurice --- C4/Acquisition.pm | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index d7f0e25e56..96d9ae5ae1 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -2150,7 +2150,6 @@ sub GetLateOrders { AND aqbasket.closedate IS NOT NULL AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') "; - my $having = ""; if ($dbdriver eq "mysql") { $select .= " aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, @@ -2161,7 +2160,7 @@ sub GetLateOrders { $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ; push @query_params, $delay; } - $having = "HAVING quantity <> 0"; + $from .= " AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0"; } else { # FIXME: account for IFNULL as above $select .= " @@ -2173,6 +2172,7 @@ sub GetLateOrders { $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) "; push @query_params, $delay; } + $from .= " AND aqorders.quantity <> 0"; } if (defined $supplierid) { $from .= ' AND aqbasket.booksellerid = ? '; @@ -2203,7 +2203,7 @@ sub GetLateOrders { push @query_params, C4::Context->userenv->{branch}; } $from .= " AND orderstatus <> 'cancelled' "; - my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier"; + my $query = "$select $from \nORDER BY latesince, basketno, borrowers.branchcode, supplier"; $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params); my $sth = $dbh->prepare($query); $sth->execute(@query_params); -- 2.14.2