recovery

[Michael Vrable]

On Fri, Nov 02, 2007 at 09:34:39AM -0400, Derek Atkins wrote:
>Michael Vrable <vrable at cs.hmc.edu> writes:
>>     SELECT * from Split where Split.amount_num * 3 = Split.amount_denom * 1;
>
> What size integers will it use internally?  Will it deal with a 64-bit 
> number multiplied by a 64-bit number?  But yes, this is certainly one 
> way to beat the rounding problem (and indeed it's what gnucash does 
> internally with some special "128-bit" math routines.

Probably implementation specific; I don't know what the SQL 
specification has to say on the matter.

I believe SQLite is limited to 64-bit arithmetic (see 
http://www.sqlite.org/datatype3.html):
     sqlite> select 123456789 * 123456789;
     15241578750190521
     sqlite> select 123456789 * 123456789 * 123456789;
     -2204193661661244627

With PostgreSQL, on the other hand:
     => select 1234567890123456789 * 123456789;
     ERROR:  bigint out of range
     => select cast(1234567890123456789 as numeric(100,0)) * 123456789;
     152415787517146788750190521
Here, the numeric(100,0) type will support up to 100-decimal digit 
integers.  The value can be tweaked to give the necessary precision; 
something about numeric(39,0) should do.  I don't know of a way to get 
128-bit integers specifically.

The NUMERIC type and CAST might be part of the standard, but I haven't 
checked.  MySQL supports them (it limits NUMERIC to 65 digits, which is 
enough), and probably other databases do as well.  However, as noted 
above, SQLite does not have the NUMERIC type.

--Michael Vrable