[FIXED] How to avoid floating-point arithmetic in a MySQL query?

Issue

Say I have two columns, A and B, which are of type DECIMAL(9,2).

Now, say I run the following query:

SELECT SUM(A) / SUM(B)

My understanding is that this division won't be done through floating-point arithmetic as both columns are of the DECIMAL type. Is this correct?

Also, say I run the following query:

SELECT SUM(A) / SUM(B) * 100

How will the 100 affect the query? Will this somehow cause MySQL to do floating-point arithmetic? If so, how can I avoid it?

Solution

How will the 100 affect the query? Will this somehow cause MySQL to do floating-point arithmetic? If so, how can I avoid it?

100 is an exact-value numeric literal, here we have decimal divided by decimal times decimal(integer) => result is still decimal(but with higher precision).

Numeric Literals

Exact-value numeric literals have an integer part or fractional part, or both. They may be signed. Examples: 1, .2, 3.4, -5, -6.78, +9.10.

Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Two numbers that look similar may be treated differently. For example, 2.34 is an exact-value (fixed-point) number, whereas 2.34E0 is an approximate-value (floating-point) number.

Example:

CREATE TABLE t(A DECIMAL(9,2), B DECIMAL(9,2));

CREATE TABLE r1 AS SELECT SUM(A) / SUM(B) AS result FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r1';
+--------------+---------------+
| COLUMN_NAME  |  COLUMN_TYPE  |
+--------------+---------------+
| result       | decimal(37,6) |
+--------------+---------------+

CREATE TABLE r2 AS SELECT SUM(A) / SUM(B) * 100 AS result FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r2';
+--------------+---------------+
| COLUMN_NAME  |  COLUMN_TYPE  |
+--------------+---------------+
| result       | decimal(40,6) |
+--------------+---------------+

Now using approximate value numeric literal 100E0:

CREATE TABLE r3 AS SELECT SUM(A) / SUM(B) * 100E0 AS result FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r3';
+--------------+-------------+
| COLUMN_NAME  | COLUMN_TYPE |
+--------------+-------------+
| result       | double      |
+--------------+-------------+

db<>fiddle demo

---

It is worth noting that some operations may look exactly the same but return different data types. For example:

CREATE TABLE r4 AS SELECT POWER(A,2) AS result, A*A AS result2 FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r4';
+--------------+---------------+
| COLUMN_NAME  |  COLUMN_TYPE  |
+--------------+---------------+
| result       | double        |
| result2      | decimal(18,4) |
+--------------+---------------+

Answered By - Lukasz Szozda

Answer Checked By - Gilberto Lyons (PHPFixing Admin)