From 24f780e22f68fb0e5968ae065cf0f73a9c978799 Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Mon, 18 Nov 2019 11:20:46 +0100
Subject: [PATCH] Bug 23982: (bug 23624 follow-up) Handle SQL error caused by
 derived table

Caused by
  commit bca4453c50d8b5b1d3c7029ded2b522a9a2bf868
  Bug 23624: (QA follow-up) Optimize even more

A report like:
SELECT * FROM issues JOIN borrowers USING (borrowernumber)

will have two borrowernumber columns - SQL will give us there rsults,
but if we try to wrap them in a SELECT COUNT(*) FROM (report) it throws
a duplicated column error.

This patch suggests to execute the query the old way if the derived
table optimization failed.

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
---
 C4/Reports/Guided.pm | 38 ++++++++++++++++++++++++++++----------
 1 file changed, 28 insertions(+), 10 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 705984d397..341c78bd91 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -425,19 +425,37 @@ sub nb_rows {
         $derived_name .= 'x';
     }
 
-    my $sth = C4::Context->dbh->prepare(qq{
-        SELECT COUNT(*) FROM
-        ( $sql ) $derived_name
-    });
 
-    $sth->execute();
+    my $dbh = C4::Context->dbh;
+    my ( $sth, $n );
+
+    my $RaiseError = $dbh->{RaiseError};
+    my $PrintError = $dbh->{PrintError};
+    $dbh->{RaiseError} = 1;
+    $dbh->{PrintError} = 0;
+    eval {
+        $sth = $dbh->prepare(qq{
+            SELECT COUNT(*) FROM
+            ( $sql ) $derived_name
+        });
 
-    if ( $sth->errstr ) {
-        return 0;
-    }
-    else {
-       return $sth->fetch->[0];
+        $sth->execute();
+    };
+    $dbh->{RaiseError} = $RaiseError;
+    $dbh->{PrintError} = $PrintError;
+    if ($@) { # To catch "Duplicate column name" caused by the derived table, or any other syntax error
+        $sth = $dbh->prepare($sql);
+        $sth->execute;
+        # Loop through the complete results, fetching 1,000 rows at a time.  This
+        # lowers memory requirements but increases execution time.
+        while (my $rows = $sth->fetchall_arrayref(undef, 1000)) {
+            $n += @$rows;
+        }
+        return $n;
     }
+
+    my $results = $sth->fetch;
+    return $results ? $results->[0] : 0;
 }
 
 =head2 execute_query
-- 
2.39.5