From 24f780e22f68fb0e5968ae065cf0f73a9c978799 Mon Sep 17 00:00:00 2001 From: Jonathan Druart 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 Signed-off-by: Nick Clemens Signed-off-by: Martin Renvoize --- 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