misc/cronjobs/update_totalissues.pl when issuing statistics table 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. 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')
Created attachment 98040 [details] [review] 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
Created attachment 99474 [details] [review] 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 <bgkriegel@gmail.com> Work as described following test plan. totalissues is calculated correctly, incremental works ok. No errors
Created attachment 99486 [details] [review] 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 <bgkriegel@gmail.com> Work as described following test plan. totalissues is calculated correctly, incremental works ok. No errors Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Nice work everyone! Pushed to master for 20.05
Pushed to 19.11.x for 19.11.05
backported to 19.05.x for 19.05.10
Backported to 18.11.x for 18.11.16