SQLite Forum

Hello !
Doing some tests I found that summing floating points with sqlite gives slightly different results compared to C, I understand that floating point arithmetic with big and small values is problematic and I'm leaving it here just in case someone can see a way to improve it.

====
create table S(
	id integer primary key not null,
	value real
);

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<6000008)
insert into S select x as id, iif((x % 2) == 0, 0.0000000123, 12345.000000000) as value
from cnt;

--select * from S limit 5;

select printf("count %d, sum: %35.15f", count(*), sum(value)) from S;
====
sqlite3 < test-sum.sql
====
count 6000008, sum:         37035049380.000150000000000
====

C
====
#include <stdio.h>

int main()
{
	#define DSIZE 60000008
	static double d[DSIZE];

	for (int i = 0; i < DSIZE; ++i)
	{
	      d[i] = (i % 2) == 0 ? 0.0000000123 : 12345.0000000001;
	}

	double sum = 0.0;
	for (int i = 0; i < DSIZE; ++i)
	{
	      sum += d[i];
	}

	printf("count: %d, sum: %35.15f\n", DSIZE, sum);
	return 0;
}
====
./test-sum
====
count: 60000008, sum:        370350049380.000183105468750
====

C with libgmp
====
#include <stdio.h>
#include <gmp.h>

int main()
{
	#define DSIZE 60000008
	mpf_t sum, a, b;
	mpf_init(sum);
	mpf_init(a);
	mpf_init(b);
	mpf_set_d(sum,0.0);
	mpf_set_str(a, "0.0000000123", 10);
	mpf_set_str(b,"12345.0000000001", 10);

	for (int i = 0; i < DSIZE; ++i)
	{
	      mpf_add(sum, sum, (i % 2) == 0 ? a : b);
	}

	printf("count: %d, sum: ", DSIZE);
	mpf_out_str(stdout, 10, 35, sum);
	puts("\n");
	return 0;
}
====
./test-sum-gmp
====
count: 60000008, sum: 0.37035004938037200005e12
====

GLPK
====
param  dsize := 6000008;
set S := {1..dsize};
param d { i in S} := if ((i mod 2) == 0) then 0.0000000123 else 12345.0000000001;

printf "count: %d, sum: %35.15f\n", card(S), sum {i in S} d[i];
end;
====
glpsol -m test-sum.mod
====
count: 6000008, sum:         37035049380.000160217285156
====