From 7a92d126df700be6ffcfbc3a371a04808714f2d6 Mon Sep 17 00:00:00 2001 From: Fridolin Somers Date: Tue, 28 Jan 2020 16:09:20 +0100 Subject: [PATCH] Bug 24527: misc/cronjobs/update_totalissues.pl problem with multiple items misc/cronjobs/update_totalissues.pl when issuing statistics table, no-incremental, has a problem for biblio with multiple items. The first SQL query gets the biblios linked to items with issues in statistics : SELECT biblio.biblionumber, COUNT(statistics.itemnumber) FROM biblio LEFT JOIN items ON (biblio.biblionumber=items.biblionumber) LEFT JOIN statistics ON (items.itemnumber=statistics.itemnumber) WHERE statistics.type = 'issue' $limit GROUP BY biblio.biblionumber The second SQL query is : SELECT biblio.biblionumber, 0 FROM biblio LEFT JOIN items ON (biblio.biblionumber=items.biblionumber) LEFT JOIN statistics ON (items.itemnumber=statistics.itemnumber) WHERE statistics.itemnumber IS NULL GROUP BY biblio.biblionumber The problem is that this second query will set to 0 where ANY item has no entry in statistics table. So when running it sets 0 to the biblio that had a value from first query. I think the best fix is to use "WHERE statistics.type = 'issue'" inside que JOIN : LEFT JOIN statistics ON (items.itemnumber=statistics.itemnumber AND statistics.type = 'issue') Test plan : 1) Begin with an empty database 2) Create a biblio 1 with no items 3) Create a biblio 2 with 3 items 4) Create a biblio 3 with 2 items 5) Checkout and checkin all items of biblio 2 6) Checkout and checkin the firt item of biblio 3 7) run misc/cronjobs/update_totalissues.pl --use-stats -v 8) Check biblio 1 has biblioitems.totalissues = 0 9) Check biblio 2 has biblioitems.totalissues = 3 10) Without patch the biblio 3 has biblioitems.totalissues = 0 11) With patch the biblio 3 has biblioitems.totalissues = 1 12) Check misc/cronjobs/update_totalissues.pl --incremental is OK Signed-off-by: Bernardo Gonzalez Kriegel Signed-off-by: Jonathan Druart Signed-off-by: Martin Renvoize Signed-off-by: Joy Nelson (cherry picked from commit 91da9841d78d26db205c6493c27c174c20e7b7c2) Signed-off-by: Lucas Gass (cherry picked from commit f6d61ce5e07c633143843604722f29b0d92d8fce) Signed-off-by: Hayley Mapley --- misc/cronjobs/update_totalissues.pl | 18 ++++++------------ 1 file changed, 6 insertions(+), 12 deletions(-) diff --git a/misc/cronjobs/update_totalissues.pl b/misc/cronjobs/update_totalissues.pl index affa2a7e34..45ca037047 100755 --- a/misc/cronjobs/update_totalissues.pl +++ b/misc/cronjobs/update_totalissues.pl @@ -136,22 +136,16 @@ sub process_stats { $dt->subtract( $units{$unit} => $1 ) ); } my $limit = ''; - $limit = " AND statistics.datetime >= ?" if ( $interval || $since ); + $limit = " WHERE statistics.datetime >= ?" if ( $interval || $since ); my $query = -"SELECT biblio.biblionumber, COUNT(statistics.itemnumber) FROM biblio LEFT JOIN items ON (biblio.biblionumber=items.biblionumber) LEFT JOIN statistics ON (items.itemnumber=statistics.itemnumber) WHERE statistics.type = 'issue' $limit GROUP BY biblio.biblionumber;"; +"SELECT biblio.biblionumber, COUNT(statistics.itemnumber) FROM biblio\ + LEFT JOIN items ON (biblio.biblionumber=items.biblionumber)\ + LEFT JOIN statistics ON (items.itemnumber=statistics.itemnumber AND statistics.type = 'issue') + $limit\ + GROUP BY biblio.biblionumber"; process_query( $query, $limit ); - unless ($incremental) { - $query = -"SELECT biblio.biblionumber, 0 FROM biblio LEFT JOIN items ON (biblio.biblionumber=items.biblionumber) LEFT JOIN statistics ON (items.itemnumber=statistics.itemnumber) WHERE statistics.itemnumber IS NULL GROUP BY biblio.biblionumber;"; - process_query( $query, '' ); - - $query = -"SELECT biblio.biblionumber, 0 FROM biblio LEFT JOIN items ON (biblio.biblionumber=items.biblionumber) WHERE items.itemnumber IS NULL GROUP BY biblio.biblionumber;"; - process_query( $query, '' ); - } - $dbh->commit(); } -- 2.39.5