This appears to be a bug due to inaccuracies in floating point math. I've been unable to reproduce it on demand. Sometimes when using the "Write off selected" button, Koha will give the error that the payment is more than the amount owed, even though they are the same. The solution I've implemented is to move the summation from Perl code to the database query. This video demonstrates the issue and afterward, shows the error goes away after the patch is applied: https://monosnap.com/file/pG69HC7iI9mU9kkuoCtbkVzVTffKlE
Created attachment 107471 [details] [review] Bug 26076: Paying selected accountlines in full may result in the error "You must pay a value less than or equal to $x" This appears to be a bug due to inaccuracies in floating point math. I've been unable to reproduce it on demand. Sometimes when using the "Write off selected" button, Koha will give the error that the payment is more than the amount owed, even though they are the same. The solution I've implemented is to move the summation from Perl code to the database query. This video demonstrates the issue and afterward, shows the error goes away after the patch is applied: https://monosnap.com/file/pG69HC7iI9mU9kkuoCtbkVzVTffKlE Test Plan: 1) Apply this patch 2) Restart all the things! 3) Verify that "Write off selected" functions as usual
(In reply to Kyle M Hall from comment #1) > Created attachment 107471 [details] [review] [review] > Bug 26076: Paying selected accountlines in full may result in the error "You > must pay a value less than or equal to $x" > > This appears to be a bug due to inaccuracies in floating point math. I've > been unable to reproduce it on demand. Sometimes when using the "Write off > selected" button, Koha will give the error that the payment is more than the > amount owed, even though they are the same. The solution I've implemented is > to move the summation from Perl code to the database query. This video > demonstrates the issue and afterward, shows the error goes away after the > patch is applied: https://monosnap.com/file/pG69HC7iI9mU9kkuoCtbkVzVTffKlE > > Test Plan: > 1) Apply this patch > 2) Restart all the things! > 3) Verify that "Write off selected" functions as usual Hi Kyle, I am unable to reproduce the issue. I have checked multiple float values for ex: 114.25, 102.21, 41.36.
> I am unable to reproduce the issue. I have checked multiple float values for > ex: 114.25, 102.21, 41.36. Same here. The bug clearly exists as I demonstrated in the video, but I can't reproduce it on demand either.
Created attachment 107503 [details] [review] Bug 26076: Paying selected accountlines in full may result in the error "You must pay a value less than or equal to $x" I have given the signoff but unable reproduce the issue.
(In reply to Kyle M Hall from comment #3) > > I am unable to reproduce the issue. I have checked multiple float values for > > ex: 114.25, 102.21, 41.36. > > Same here. The bug clearly exists as I demonstrated in the video, but I > can't reproduce it on demand either. Adding Katrin
How to reproduce: 1) Create manual invoice => amount 1.00 2) Create manual credit => amount 0.011111 3) Make a payment => Click Apply -0.01 4) Click "Pay amount" and then on the next page "Confirm"
What I described in comment #6 could be of the same origin as the summation issue originally described here, maybe because in db we have 6 decimals and in GUI only 2 decimals? The proposed fix might solve that one case and we could apply this patch to fix that, but I would just like to have the commit message in that case still be fixed, now it describes the bug and not the change, please see <https://wiki.koha-community.org/wiki/Commit_messages#Examples>. Marking this FQA until the commit message is fixed (or a more generic patch is submitted to solve this class of bugs).
What I gather, the issue I mentioned in comment #6 seems to be just weird behaviour caused by not having RoundFinesAtPayment systempref enabled and not related to this.
Created attachment 107516 [details] [review] Bug 26076: Sum the amount due in the database query instead of a loop in Perl This appears to be a bug due to inaccuracies in floating point math. I've been unable to reproduce it on demand. Sometimes when using the "Write off selected" button, Koha will give the error that the payment is more than the amount owed, even though they are the same. The solution I've implemented is to move the summation from Perl code to the database query. This video demonstrates the issue and afterward, shows the error goes away after the patch is applied: https://monosnap.com/file/pG69HC7iI9mU9kkuoCtbkVzVTffKlE Test Plan: 1) Apply this patch 2) Restart all the things! 3) Verify that "Write off selected" functions as usual Signed-off-by: Amit Gupta <amit.gupta@informaticsglobal.com>
I should also note that the library in my video has RoundFinesAtPayment disabled.
Thanks for the commit message fix, Kyle! I was about to pass QA for this... But then I ended up with this error on my last test: DBIx::Class::Storage::DBI::_dbh_execute(): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at /kohadevbox/koha/members/paycollect.pl line 157 at /usr/share/perl5/DBIx/Class/Exception.pm line 77 Without the patch provided here the error doesn't happen. How to reproduce: 0) Apply patch provided in this bug report & restart plack 1) Have RoundFinesAtPayment = No (not sure this is relevant though) 2) Create 3 manual invoices with the amount of 3.33 3) Make a payment > write off selected 4) The error should appear.
(In reply to Joonas Kylmälä from comment #11) > Thanks for the commit message fix, Kyle! I was about to pass QA for this... > But then I ended up with this error on my last test: > > DBIx::Class::Storage::DBI::_dbh_execute(): Mixing of GROUP columns > (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no > GROUP BY clause at /kohadevbox/koha/members/paycollect.pl line 157 > at /usr/share/perl5/DBIx/Class/Exception.pm line 77 > > Without the patch provided here the error doesn't happen. How to reproduce: > > 0) Apply patch provided in this bug report & restart plack > 1) Have RoundFinesAtPayment = No (not sure this is relevant though) > 2) Create 3 manual invoices with the amount of 3.33 > 3) Make a payment > write off selected > 4) The error should appear. Interesting, I have no idea why I didn't get this error in my testing! Followup incoming!
Created attachment 107589 [details] [review] Bug 26076: (QA Follow up) Prevent error by using a fresh resultset
Created attachment 107590 [details] [review] Bug 26076: Sum the amount due in the database query instead of a loop in Perl This appears to be a bug due to inaccuracies in floating point math. I've been unable to reproduce it on demand. Sometimes when using the "Write off selected" button, Koha will give the error that the payment is more than the amount owed, even though they are the same. The solution I've implemented is to move the summation from Perl code to the database query. This video demonstrates the issue and afterward, shows the error goes away after the patch is applied: https://monosnap.com/file/pG69HC7iI9mU9kkuoCtbkVzVTffKlE Test Plan: 1) Apply this patch 2) Restart all the things! 3) Verify that "Write off selected" functions as usual Signed-off-by: Amit Gupta <amit.gupta@informaticsglobal.com>
Created attachment 107591 [details] [review] Bug 26076: (QA Follow up) Prevent error by using a fresh resultset
I was able to reproduce the error: When adding the manual invoices for testing, enter values with more than 2 decimals. But it only prevents me from paying when the amount outstanding and the amount being paid are off by one cent. I believe this patch doesn't hurt things, but it would be great to see what's going on in the db when this error appears.
Found Joonas comment now: (In reply to Joonas Kylmälä from comment #6) > How to reproduce: > 1) Create manual invoice => amount 1.00 > 2) Create manual credit => amount 0.011111 > 3) Make a payment => Click Apply -0.01 > 4) Click "Pay amount" and then on the next page "Confirm" Following this test plan, I can still not pay the remaining fine off, the error is as before: You must pay a value less than or equal to 0.99.
(In reply to Katrin Fischer from comment #17) > Found Joonas comment now: > > (In reply to Joonas Kylmälä from comment #6) > > How to reproduce: > > 1) Create manual invoice => amount 1.00 > > 2) Create manual credit => amount 0.011111 > > 3) Make a payment => Click Apply -0.01 > > 4) Click "Pay amount" and then on the next page "Confirm" > > Following this test plan, I can still not pay the remaining fine off, the > error is as before: > > You must pay a value less than or equal to 0.99. Katrin, this error above I erroneously identified of being related to this. The error Kyle reported here was a rounding error and this error I put steps to reproduce intentionally creates more decimals than the intranet interface can show or accepts payments for. Even if the intranet interface was fixed the rounding error would still be there. Moving back to "Signed off".
> > You must pay a value less than or equal to 0.99. > > Katrin, this error above I erroneously identified of being related to this. > The error Kyle reported here was a rounding error and this error I put steps > to reproduce intentionally creates more decimals than the intranet interface > can show or accepts payments for. Even if the intranet interface was fixed > the rounding error would still be there. Moving back to "Signed off". Thx Joonas. So we still have no way to recreate the rounding issue, is that correct?
Joonas, I think you are in deeper here than me - could you check the follow-up and finish up here?
Created attachment 108938 [details] [review] Bug 26076: Sum the amount due in the database query instead of a loop in Perl This appears to be a bug due to inaccuracies in floating point math. I've been unable to reproduce it on demand. Sometimes when using the "Write off selected" button, Koha will give the error that the payment is more than the amount owed, even though they are the same. The solution I've implemented is to move the summation from Perl code to the database query. This video demonstrates the issue and afterward, shows the error goes away after the patch is applied: https://monosnap.com/file/pG69HC7iI9mU9kkuoCtbkVzVTffKlE Test Plan: 1) Apply this patch 2) Restart all the things! 3) Verify that "Write off selected" functions as usual Signed-off-by: Amit Gupta <amit.gupta@informaticsglobal.com> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
Created attachment 108939 [details] [review] Bug 26076: (QA Follow up) Prevent error by using a fresh resultset Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de>
As the error has proven hard to replicate I am passing this mostly on base of regression testing.
Pushed to master for 20.11, thanks to everybody involved!
backported to 20.05.x for 20.05.04
backported to 19.11.x for 19.11.10
Not backported to oldoldstable (19.05.x). Feel free to ask if it's needed.