Created attachment 95215 [details] [review] Bug 24002: Incorrect DATE value: '00-00-0000' in NewsChannels.pm % prove xt/author/valid-templates.t DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement " SELECT opac_news.*,timestamp AS newdate, borrowers.title AS author_title, borrowers.firstname AS author_firstname, borrowers.surname AS author_surname FROM opac_news LEFT JOIN borrowers on borrowers.borrowernumber = opac_news.borrowernumber WHERE ( expirationdate >= CURRENT_DATE() OR expirationdate IS NULL OR expirationdate = '00-00-0000' ) AND DATE(timestamp) < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND (opac_news.lang = '' OR opac_news.lang = ?) AND (opac_news.branchcode IS NULL OR opac_news.branchcode = ?) ORDER BY number " with ParamValues: 0='OpacNavRight_', 1=''] at /kohadevbox/koha/C4/NewsChannels.pm line 216.
Created attachment 95216 [details] [review] Bug 24002: Incorrect DATE value: '' in C4/Bookseller.pm t/db_dependent/Bookseller.t .. 3/86 DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement " SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id WHERE ( datereceived = '' OR datereceived IS NULL OR aqorders.quantityreceived < aqorders.quantity ) AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 AND aqbasket.closedate IS NOT NULL "] at /kohadevbox/koha/C4/Bookseller.pm line 100.
Created attachment 95217 [details] [review] Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Serials.pm DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT serialid,serialseq, status, planneddate, publisheddate, publisheddatetext, notes, routingnotes FROM serial WHERE subscriptionid = ? AND status IN ( 2,4,41,42,43,44,5 ) ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC " with ParamValues: 0=8] at /kohadevbox/koha/C4/Serials.pm line 688.
Created attachment 95218 [details] [review] Bug 24002: Incorrect DATE value: '00-00-0000' in C4/Serials.pm t/db_dependent/Biblio.t .. 2/12 DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement " SELECT serial.serialid, serial.serialseq, serial.planneddate, serial.publisheddate, serial.publisheddatetext, serial.status, serial.notes as notes, year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year, biblio.title as bibliotitle, subscription.branchcode AS branchcode, subscription.subscriptionid AS subscriptionid FROM serial LEFT JOIN subscription ON (serial.subscriptionid=subscription.subscriptionid) LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber WHERE subscription.biblionumber = ? ORDER BY year DESC, IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC, serial.subscriptionid " with ParamValues: 0='446'] at /kohadevbox/koha/C4/Serials.pm line 482.
Created attachment 95219 [details] [review] Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Acquisition.pm t/db_dependent/Acquisition/close_reopen_basket.t .. DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement " SELECT COUNT( DISTINCT( biblionumber ) ) FROM aqorders WHERE basketno = ? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012. DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement " SELECT COUNT( DISTINCT( biblionumber ) ) FROM aqorders WHERE basketno = ? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012. And anticipating other failures
Created attachment 95220 [details] [review] Bug 24002: Incorrect DATE value: '' in C4/Acquisition.pm DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement " SELECT aqbasket.basketno, aqorders.ordernumber, DATE(aqbasket.closedate) AS orderdate, aqbasket.basketname AS basketname, aqbasket.basketgroupid AS basketgroupid, aqbasketgroups.name AS basketgroupname, aqorders.rrp AS unitpricesupplier, aqorders.ecost AS unitpricelib, aqorders.claims_count AS claims_count, aqorders.claimed_date AS claimed_date, aqbudgets.budget_name AS budget, borrowers.branchcode AS branch, aqbooksellers.name AS supplier, aqbooksellers.id AS supplierid, biblio.author, biblio.title, biblioitems.publishercode AS publisher, biblioitems.publicationyear, ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, DATEDIFF(CAST(now() AS date),closedate) AS latesince FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id WHERE aqorders.basketno = aqbasket.basketno AND ( datereceived = '' OR datereceived IS NULL OR aqorders.quantityreceived < aqorders.quantity ) AND aqbasket.closedate IS NOT NULL AND aqorders.datecancellationprinted IS NULL AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 AND orderstatus <> 'cancelled' ORDER BY latesince, basketno, borrowers.branchcode, supplier" with ParamValues: 0=0] at /kohadevbox/koha/C4/Acquisition.pm line 2248.
Created attachment 95221 [details] [review] Bug 24002: One more occurrence of datecancellationprinted
Created attachment 95222 [details] [review] Bug 24002: Incorrect DATE value: '00-00-0000' in NewsChannels.pm % prove xt/author/valid-templates.t DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement " SELECT opac_news.*,timestamp AS newdate, borrowers.title AS author_title, borrowers.firstname AS author_firstname, borrowers.surname AS author_surname FROM opac_news LEFT JOIN borrowers on borrowers.borrowernumber = opac_news.borrowernumber WHERE ( expirationdate >= CURRENT_DATE() OR expirationdate IS NULL OR expirationdate = '00-00-0000' ) AND DATE(timestamp) < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND (opac_news.lang = '' OR opac_news.lang = ?) AND (opac_news.branchcode IS NULL OR opac_news.branchcode = ?) ORDER BY number " with ParamValues: 0='OpacNavRight_', 1=''] at /kohadevbox/koha/C4/NewsChannels.pm line 216. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 95223 [details] [review] Bug 24002: Incorrect DATE value: '' in C4/Bookseller.pm t/db_dependent/Bookseller.t .. 3/86 DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement " SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id WHERE ( datereceived = '' OR datereceived IS NULL OR aqorders.quantityreceived < aqorders.quantity ) AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 AND aqbasket.closedate IS NOT NULL "] at /kohadevbox/koha/C4/Bookseller.pm line 100. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 95224 [details] [review] Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Serials.pm DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement "SELECT serialid,serialseq, status, planneddate, publisheddate, publisheddatetext, notes, routingnotes FROM serial WHERE subscriptionid = ? AND status IN ( 2,4,41,42,43,44,5 ) ORDER BY IF(publisheddate<>'0000-00-00',publisheddate,planneddate) DESC " with ParamValues: 0=8] at /kohadevbox/koha/C4/Serials.pm line 688. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 95225 [details] [review] Bug 24002: Incorrect DATE value: '00-00-0000' in C4/Serials.pm t/db_dependent/Biblio.t .. 2/12 DBD::mysql::st execute failed: Incorrect DATE value: '00-00-0000' [for Statement " SELECT serial.serialid, serial.serialseq, serial.planneddate, serial.publisheddate, serial.publisheddatetext, serial.status, serial.notes as notes, year(IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate)) as year, biblio.title as bibliotitle, subscription.branchcode AS branchcode, subscription.subscriptionid AS subscriptionid FROM serial LEFT JOIN subscription ON (serial.subscriptionid=subscription.subscriptionid) LEFT JOIN aqbooksellers on subscription.aqbooksellerid=aqbooksellers.id LEFT JOIN biblio on biblio.biblionumber=subscription.biblionumber WHERE subscription.biblionumber = ? ORDER BY year DESC, IF(serial.publisheddate="00-00-0000",serial.planneddate,serial.publisheddate) DESC, serial.subscriptionid " with ParamValues: 0='446'] at /kohadevbox/koha/C4/Serials.pm line 482. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 95226 [details] [review] Bug 24002: Incorrect DATE value: '0000-00-00' in C4/Acquisition.pm t/db_dependent/Acquisition/close_reopen_basket.t .. DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement " SELECT COUNT( DISTINCT( biblionumber ) ) FROM aqorders WHERE basketno = ? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012. DBD::mysql::st execute failed: Incorrect DATE value: '0000-00-00' [for Statement " SELECT COUNT( DISTINCT( biblionumber ) ) FROM aqorders WHERE basketno = ? AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') " with ParamValues: 0='2'] at /kohadevbox/koha/C4/Acquisition.pm line 3012. And anticipating other failures Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Created attachment 95227 [details] [review] Bug 24002: Incorrect DATE value: '' in C4/Acquisition.pm DBD::mysql::st execute failed: Incorrect DATE value: '' [for Statement " SELECT aqbasket.basketno, aqorders.ordernumber, DATE(aqbasket.closedate) AS orderdate, aqbasket.basketname AS basketname, aqbasket.basketgroupid AS basketgroupid, aqbasketgroups.name AS basketgroupname, aqorders.rrp AS unitpricesupplier, aqorders.ecost AS unitpricelib, aqorders.claims_count AS claims_count, aqorders.claimed_date AS claimed_date, aqbudgets.budget_name AS budget, borrowers.branchcode AS branch, aqbooksellers.name AS supplier, aqbooksellers.id AS supplierid, biblio.author, biblio.title, biblioitems.publishercode AS publisher, biblioitems.publicationyear, ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, DATEDIFF(CAST(now() AS date),closedate) AS latesince FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id WHERE aqorders.basketno = aqbasket.basketno AND ( datereceived = '' OR datereceived IS NULL OR aqorders.quantityreceived < aqorders.quantity ) AND aqbasket.closedate IS NOT NULL AND aqorders.datecancellationprinted IS NULL AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0 AND orderstatus <> 'cancelled' ORDER BY latesince, basketno, borrowers.branchcode, supplier" with ParamValues: 0=0] at /kohadevbox/koha/C4/Acquisition.pm line 2248. Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Ran tests again M8, saw failures.. applied patches and re-ran tests, full Pass. Looks good, signing off. Minor concern is whether we might be missing some DB updates to ensure all tables are filled with expected data to start with..
(In reply to Martin Renvoize from comment #14) > Minor concern is whether we might be missing some DB updates to ensure all > tables are filled with expected data to start with.. Hum actually it's too late. We cannot longer write such query: update opac_news set expirationdate=NULL WHERE expirationdate="0000-00-00"; As it will fail: ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'expirationdate' at row 1
So really we shouldn't have any left around already. :)
Trivial patches, Passing QA
Nice work! Pushed to master for 19.11.00
Created attachment 99118 [details] [review] Bug 24002: (follow-up) Incorrect DATE value: '00-00-0000' in C4/Serials.pm Oops, wrong copy/paste here! The column order changes was not expected.
(In reply to Jonathan Druart from comment #19) > Created attachment 99118 [details] [review] [review] > Bug 24002: (follow-up) Incorrect DATE value: '00-00-0000' in C4/Serials.pm > > Oops, wrong copy/paste here! The column order changes was not expected. Sorry, it's part of 19.11, I will open a new bug report tomorrow.
Follow-up patch moved to bug 24677.
Nice work everyone! Pushed to master for 20.05
(In reply to Martin Renvoize from comment #22) > Nice work everyone! > > Pushed to master for 20.05 Should not have been pushed regarding my last 2 comments :-/