sum of big integer in varchar is different from varchar itselt

Question

Assume there is only one record:

id	big_varchar
1	1433549062949664079826

I find that the result is different for two queries:

• query 1: select sum(big_varchar) from tbl; // result = 1.4335490629496642e21

• query 2: select big_varchar from tbl; // result = 1433549062949664079826

What should I do if I want the result using the exact number(i.e. 1433549062949664079826) when doing SUM ?

Answer 1

When you use SUM() then the column datatype is converted implicitly, and DOUBLE datatype is used. So use explicit convertion.

CREATE TABLE tbl SELECT 1 id, '1433549062949664079826' big_varchar;
select sum(big_varchar) from tbl;
select big_varchar from tbl;
select sum(CAST(big_varchar AS UNSIGNED)) from tbl;
select CAST(big_varchar AS UNSIGNED) from tbl;
select sum(CAST(big_varchar AS DECIMAL(32))) from tbl;
select CAST(big_varchar AS DECIMAL(32)) from tbl;

|      sum(big_varchar) |
| --------------------: |
| 1.4335490629496642e21 |

| big_varchar            |
| :--------------------- |
| 1433549062949664079826 |

| sum(CAST(big_varchar AS UNSIGNED)) |
| ---------------------------------: |
|               18446744073709551615 |

| CAST(big_varchar AS UNSIGNED) |
| ----------------------------: |
|          18446744073709551615 |

| sum(CAST(big_varchar AS DECIMAL(32))) |
| ------------------------------------: |
|                1433549062949664079826 |

| CAST(big_varchar AS DECIMAL(32)) |
| -------------------------------: |
|           1433549062949664079826 |

db<>fiddle here