Bugzilla – Attachment 95545 Details for
Bug 23982
Count/pagination broken for reports with duplicated column names
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 23982: (bug 23624 follow-up) Handle SQL error caused by derived table
Bug-23982-bug-23624-follow-up-Handle-SQL-error-cau.patch (text/plain), 2.31 KB, created by
Martin Renvoize (ashimema)
on 2019-11-19 12:47:41 UTC
(
hide
)
Description:
Bug 23982: (bug 23624 follow-up) Handle SQL error caused by derived table
Filename:
MIME Type:
Creator:
Martin Renvoize (ashimema)
Created:
2019-11-19 12:47:41 UTC
Size:
2.31 KB
patch
obsolete
>From 76d67b7256015ae1f51406360082a2b62782e5ad 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> >--- > 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.20.1
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 23982
:
95479
|
95545
|
95548
|
95593