From cbd566caf6e827e67c20d7d06de2e5bd7036dff5 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Mon, 5 Feb 2018 19:10:34 -0300
Subject: [PATCH] Bug 20144: [sql_modes] Remove HAVING clause in
 GetBorrowersToExpunge

Special attention needed here!

Fix for:
Non-grouping field 'currentissue' is used in HAVING clause

t/db_dependent/Members.t

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>
Signed-off-by: Julian Maurice <julian.maurice@biblibre.com>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
---
 C4/Members.pm | 35 ++++++++++++++++++++---------------
 1 file changed, 20 insertions(+), 15 deletions(-)

diff --git a/C4/Members.pm b/C4/Members.pm
index 2729d360e5..90ebb54afe 100644
--- a/C4/Members.pm
+++ b/C4/Members.pm
@@ -907,25 +907,27 @@ sub GetBorrowersToExpunge {
 
     my $dbh   = C4::Context->dbh;
     my $query = q|
-        SELECT borrowers.borrowernumber,
-               MAX(old_issues.timestamp) AS latestissue,
-               MAX(issues.timestamp) AS currentissue
-        FROM   borrowers
-        JOIN   categories USING (categorycode)
-        LEFT JOIN (
-            SELECT guarantorid
-            FROM borrowers
-            WHERE guarantorid IS NOT NULL
-                AND guarantorid <> 0
-        ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
-        LEFT JOIN old_issues USING (borrowernumber)
-        LEFT JOIN issues USING (borrowernumber)|;
+        SELECT *
+        FROM (
+            SELECT borrowers.borrowernumber,
+                   MAX(old_issues.timestamp) AS latestissue,
+                   MAX(issues.timestamp) AS currentissue
+            FROM   borrowers
+            JOIN   categories USING (categorycode)
+            LEFT JOIN (
+                SELECT guarantorid
+                FROM borrowers
+                WHERE guarantorid IS NOT NULL
+                    AND guarantorid <> 0
+            ) as tmp ON borrowers.borrowernumber=tmp.guarantorid
+            LEFT JOIN old_issues USING (borrowernumber)
+            LEFT JOIN issues USING (borrowernumber)|;
     if ( $filterpatronlist  ){
         $query .= q| LEFT JOIN patron_list_patrons USING (borrowernumber)|;
     }
     $query .= q| WHERE  category_type <> 'S'
         AND tmp.guarantorid IS NULL
-   |;
+    |;
     my @query_params;
     if ( $filterbranch && $filterbranch ne "" ) {
         $query.= " AND borrowers.branchcode = ? ";
@@ -947,11 +949,14 @@ sub GetBorrowersToExpunge {
         $query.=" AND patron_list_id = ? ";
         push( @query_params, $filterpatronlist );
     }
-    $query.=" GROUP BY borrowers.borrowernumber HAVING currentissue IS NULL ";
+    $query .= " GROUP BY borrowers.borrowernumber";
+    $query .= q|
+        ) xxx WHERE currentissue IS NULL|;
     if ( $filterdate ) {
         $query.=" AND ( latestissue < ? OR latestissue IS NULL ) ";
         push @query_params,$filterdate;
     }
+
     warn $query if $debug;
 
     my $sth = $dbh->prepare($query);
-- 
2.39.5