Sqlite integer overflow while using BIGINT

Question

Having a sqlite3 I would like to query and sum different values. The query I'm trying to do is:

SUM(CAST(amountA AS BIGINT)) as totalA, SUM(CAST(amountB AS BIGINT)) as totalB

When the query finds several results it will trigger:

error="integer overflow"

Looking at the docs of sqlite it appears that there isn't such a BIGINT, but instead, due affinity, the BIGINT is treated as an INTEGER.

Is it possible to SUM BIGINT numbers using sqlite3?

EDIT

The data type of amountA and amountB is TEXT. Changing it to INTEGER will trigger the same error when doing:

SUM(amountA) as totalA, SUM(amountB) as totalB

This is because the values I'm storing is 1000000000000000000 and when 10 values like those are stored, when summing them it will overflow as it overflows a 8 bytes signed integer

just curious: What is the data column definition that you feel a need to `cast` it? All integer types are treated as bigint in memory anyway? What happens when you sum the column directly? I would check that the `AmountA` column really does hold integer values in your table in the required range. I suspect that it is 'data value' error in a column rather than a `BIGINT` issue. — Ryan Vincent, May 26 '21 at 14:05
Sqlite has the concept of type affinity and bigint is considered integer, you will be better off by casting to Numeric or Decimal — Jayvee, May 26 '21 at 14:08
Ohh I'm using TEXT. I guess I would need to change it to INTEGER then — manolodewiner, May 26 '21 at 14:11

score 2 · Answer 1 · answered May 26 '21 at 14:45

You should store integer values as INTEGER and not TEXT, but for your issue it would not make any difference.

When using aggregate functions like SUM() any string value that appears to be integer is implicitly converted to integer and if all summed values are integers then the resulting data type is also INTEGER.
It seems that the sum of your data exceeds the max 64bit integer value supported by SQLite which is 9223372036854775807 and you get integer overflow.

What you can do is sum the numbers as real numbers by casting each value to REAL:

SUM(CAST(amountA AS REAL)) as totalA, SUM(CAST(amountB AS REAL)) as totalB

Or use SQLite's TOTAL() aggregate function which sums the numbers as REAL and never throws an integer overflow:

TOTAL(amountA) as totalA, TOTAL(amountB) as totalB

You can read more about SUM() and TOTAL() here: Built-in Aggregate Functions

Thank you, this works perfect! The values might overflow the max 64bit integer so that's why I'm saving them as TEXT — manolodewiner, May 26 '21 at 14:50
@manolodewiner then you should use TOTAL() instead of SUM(). — forpas, May 26 '21 at 14:53

Serg · Answer 2 · 2021-05-26T14:16:38.203

But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer)

If your sum fits into 64bit signed integer [-9223372036854775808 .. 9223372036854775807] it should be ok. You needn't to cast int to bigint.

Quick demo

with cte(a) as (
      select 9223372036854775807 - 100 union all
      -- uncommenting next line will cause integer overflow
      --select 1 union all
      select 100
     )
select sum(a), typeof(sum(a)) t
from cte

Result

sum(a)	t
9223372036854775807	integer

score 0 · Answer 3 · answered May 26 '21 at 14:25

Please mention your data type of amountA and amountB column. Is it integer? I suspect not.

Casting should not be needed into bigint.

SUM(amountA ) as totalA, SUM(amountB) as totalB Should be ok.

But first please share sample data of amountA and amoutB column. I am afraid that the problem is not in sum() but the datatype of the column in that table.

Sqlite integer overflow while using BIGINT

3 Answers3