[fpc-devel] Implementing TFmtBCDField - ftFmtBCD and SQLite

[LacaK]

Hi,
after doing some test with new implementation of TFmtBCDField for 
TSQLite3Connection connector I encounter this problem:

When you declare in SQLite some column as NUMERIC or DECIMAL then this 
column will have NUMERIC affinity.
CREATE TABLE t (d DECIMAL(30,7));

If you insert in such column any numeric value (quoted or unquoted):
INSERT INTO t VALUES('123456789123456789.123456');

SQLite converts such numeric value to 8-byte floating point 
representation (REAL) or 8-byte integer (INTEGER), so:
(lost of precision occurs)
SELECT * FROM t;
returns 123456789123456784


See: http://www.sqlite.org/datatype3.html#affinity
"A column with NUMERIC affinity may contain values using all five 
storage classes. When text data is inserted into a NUMERIC column, the 
storage class of the text is converted to INTEGER or REAL (in order of 
preference) if such conversion is lossless and reversible. For 
conversions between TEXT and REAL storage classes, SQLite considers the 
conversion to be lossless and reversible if the first 15 significant 
decimal digits of the number are preserved. If the lossless conversion 
of TEXT to INTEGER or REAL is not possible then the value is stored 
using the TEXT storage class. No attempt is made to convert NULL or BLOB 
values."

My original understanding was, that when I store big numbers as TEXT and 
lossless conversion to REAL can not be done, then value will be stored 
as TEXT, but in meaning in SQLite lossless is: " if the first 15 
significant decimal digits of the number are preserved" (not all digits 
only first 15!!!)

I have asked about this in sqlite-users mailing list (topic "storing big 
numbers into NUMERIC, DECIMAL columns") and results are:
(I attach some of comments from sqlite-users mailing list)

A.) if we want preserve precision of BCD values we must store them as 
BLOBs (using sqlite3_bind_blob()) ... see (1) and (2) in attachment
(with optimalization like : if BCDPrecision(...) > 15 then use 
sqlite3_bind_blob(...) else sqlite3_bind_double(...) )
Here my test shows, that if I store value like BLOB I still can do 
select like:
SELECT d+100 FROM t;
SELECT sum(d) FROM t;
SELECT * FROM t WHERE d>12345678912345678;
(it seems, that SQLite converts BLOB->TEXT->REAL ...of course precision 
is lost)

B.) does not support numbers (big exact numbers), which are not 
supported by SQLite (SQLite supports only REAL and INTEGER). ... see (3)
So DECIMAL, NUMERIC map to ftFloat if "decimal places">4 (out of range 
ftBCD) and map to ftLargeInt if "decimal places"=0

What do you think, which way to go ?

-Laco.
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: LazSqlite-bignums-discussion.txt
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20110324/f2fcee57/attachment.txt>