MySQL Bugs: #95622: Calculation error is retrieving rows

Bug #95622	Calculation error is retrieving rows
Submitted:	3 Jun 2019 19:00	Modified:	11 Jul 2019 16:45
Reporter:	James Stow	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S6 (Debug Builds)
Version:	8.0.15	OS:	Windows (Microsoft Windows 10 Home)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\jim-s\AppData\Roaming\MySQL\Workbench\log\wb.log]

How to repeat:
Bug – Arithmetic problem
select invoice_id, invoice_total, payment_total, credit_total  
from invoices	
where invoice_total > payment_total - credit_total	-- 13 rows returned, this is correct
order by vendor_id; 
Finds all the rows where the balance (invoice_total > payment_total - credit_total) balance due is greater than zero
These are columns 2, 3 & 4.
'113', '224.00', '0.00', '0.00'
'89', '85.31', '0.00', '0.00'
'110', '90.36', '0.00', '0.00'
'98', '579.42', '0.00', '0.00'
'105', '503.20', '0.00', '0.00'
'102', '20551.18', '0.00', '1200.00'
'106', '23517.58', '21221.63', '2295.95'
'112', '10976.06', '0.00', '0.00'
'78', '1962.13', '1762.13', '200.00'
'94', '52.25', '0.00', '0.00'
'99', '59.97', '0.00', '0.00'
'100', '67.92', '0.00', '0.00'
'101', '30.75', '0.00', '0.00'
select invoice_id, invoice_total, payment_total, credit_total 
from invoices	-- look for vendors with a balance due greater than zero
where invoice_total - payment_total - credit_total > 0	-- 11 rows returned, this is incorrect
order by vendor_id; 
Misses the following 2 rows
'106', '23517.58', '21221.63', '2295.95'
'78', '1962.13', '1762.13', '200.00' 
Apparently the second query only considers invoice total – payment total in the comparison with zero and ignores the credit total (2295.95 and 200.00 respectively.)

Email: jim-stow@hotmail.com

Hi,

Thank you for your bug report.

I truly do not see what is your problem. Namely, both expressions:

23517.58 - 21221.63 -  2295.95
1962.13 - 1762.13 - 200.00

are smaller then or equal to zero, if you are using floating point numbers.

Fixed point numbers might return 0 (zero), but it would still not make them greater then zero !!!!

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hello MySQL,

I have a request and I owe two apologies. I will put my request in a PS.

First, I apologize for not responding to the response to my first report. I was busy elsewhere and did not respond. Second, I apologize for incorrectly reporting the bug. I am new to SQL and I think I reversed the problem. Please allow me to re-do the bug report. (I searched for the log and could not find it on my laptop.)

The problem is the following two clauses should return the same result when searching for a balance greater than zero:
where invoice_total - payment_total - credit_total > 0
and
where invoice_total > payment_total - credit_total

both are looking for a non-zero balance.

The first is correct. It returns 11 rows, all with non-zero balances.

The second is incorrect. It returns two rows that have a zero balance. It incorrectly returns rows with an Invoice_id's of 78 and 106.

The following are the two queries with their attendant outputs. I hope this is a better explanation of the bug. I could not find the log file. I will do better at responding this time.

select invoice_id, invoice_total, payment_total, credit_total,
invoice_total - (payment_total + credit_total) as 'Amt owed' 
from invoices   
where invoice_total - payment_total - credit_total > 0;

10:55:26              select invoice_id, invoice_total, payment_total, credit_total, invoice_total - (payment_total + credit_total) as 'Amt owed'  from invoices    where invoice_total - payment_total - credit_total > 0 LIMIT 0, 1000    11 row(s) returned           0.000 sec / 0.000 sec

'89', '85.31', '0.00', '0.00', '85.31'
'94', '52.25', '0.00', '0.00', '52.25'
'98', '579.42', '0.00', '0.00', '579.42'
'99', '59.97', '0.00', '0.00', '59.97'
'100', '67.92', '0.00', '0.00', '67.92'
'101', '30.75', '0.00', '0.00', '30.75'
'102', '20551.18', '0.00', '1200.00', '19351.18'
'105', '503.20', '0.00', '0.00', '503.20'
'110', '90.36', '0.00', '0.00', '90.36'
'112', '10976.06', '0.00', '0.00', '10976.06'
'113', '224.00', '0.00', '0.00', '224.00'

select invoice_id, invoice_total, payment_total, credit_total,
invoice_total - (payment_total + credit_total) as 'Amt owed'
from invoices   
where invoice_total > payment_total - credit_total;

10:19:33              select invoice_id, invoice_total, payment_total, credit_total, invoice_total - (payment_total + credit_total) as 'Amt owed' from invoices    where invoice_total > payment_total - credit_total LIMIT 0, 1000           13 row(s) returned           0.000 sec / 0.000 sec

'78', '1962.13', '1762.13', '200.00', '0.00'
'89', '85.31', '0.00', '0.00', '85.31'
'94', '52.25', '0.00', '0.00', '52.25'
'98', '579.42', '0.00', '0.00', '579.42'
'99', '59.97', '0.00', '0.00', '59.97'
'100', '67.92', '0.00', '0.00', '67.92'
'101', '30.75', '0.00', '0.00', '30.75'
'102', '20551.18', '0.00', '1200.00', '19351.18'
'105', '503.20', '0.00', '0.00', '503.20'
'106', '23517.58', '21221.63', '2295.95', '0.00'
'110', '90.36', '0.00', '0.00', '90.36'
'112', '10976.06', '0.00', '0.00', '10976.06'
'113', '224.00', '0.00', '0.00', '224.00'

Best regards,

Jim Stow

P.S, I was trying to apply a query with a CUBE on the GROUP BY, but I could not find this in the MySQL documentation and I received an error when I tried to do it similar to a GROUP BY with a ROLLUP. Can you give me the syntax for this function or direct me to the documentation? Thank you.

Hi Mr. Stow,

What you report is still not a bug. You are using floating point numbers, where it depends a lot on the precision of the standard that you use. If you want to deal with financial calculations, you should use fixed point arithmetics.

ROLLUP was implemented many years ago (by me) and it is described in the chapter on the Optimiser. BTW, this is not a reading service and you are supposed to read all relevant parts of our Reference Manual.