SQLite INTEGER overflow in SQLDB/TSQLQuery

[kapibara]

Would declaring INTEGER column as BIGINT help?

I am using Lazarus/FPC with SQLite through SQLDB / TSQLQuery.

I store data in SQLite with millisecond timestamps, for example:

Code: Pascal  [Select][+][-]

1. CREATE TABLE mytable (
2.   ...,
3.   timestamp_ms INTEGER NOT NULL,
4.   ...)
5. );

Example value:

Code: Pascal  [Select][+][-]

1. timestamp_ms = 1780324920000

The value is stored correctly in SQLite. Queries directly in SQLite show the correct value.

However, when I read it through TSQLQuery, this gives a 32-bit overflowed value:

Code: Pascal  [Select][+][-]

1. C.Timestamp := Q.FieldByName('timestamp_ms').AsLargeInt;

The result becomes something like:

-2146207840

instead of:

1780324920000

Using AsLongint is obviously wrong because the value is too large for 32-bit, but I expected `AsLargeInt` to work.

I found a workaround:

Code: Pascal  [Select][+][-]

1. SELECT CAST(timestamp_ms AS TEXT) AS timestamp_ms_text
2. FROM mytable

and then:

Code: Pascal  [Select][+][-]

1. C.Timestamp := StrToInt64(Q.FieldByName('timestamp_ms_text').AsString);

This works correctly.

My questions:

1. Is this expected behavior with SQLite + SQLDB/TSQLQuery?
2. Does SQLDB infer the field type from the declared SQLite column type?
3. Would changing the schema from:

Code: Pascal  [Select][+][-]

1. timestamp_ms INTEGER NOT NULL

to:

Code: Pascal  [Select][+][-]

1. timestamp_ms BIGINT NOT NULL

or:

Code: Pascal  [Select][+][-]

1. timestamp_ms INT8 NOT NULL

make TSQLQuery.FieldByName(...).AsLargeInt return the correct 64-bit value?
4. Is there a recommended way to read SQLite 64-bit integer values safely through SQLDB?
5. Should I continue using CAST(timestamp_ms AS TEXT) + StrToInt64, or is there a better solution?

I know SQLite itself stores integers as up to 64-bit regardless of names like INTEGER, BIGINT, etc., but I am wondering whether the declared type name affects the field class/type chosen by SQLDB.

Environment:

Code: Pascal  [Select][+][-]

1. Lazarus/FPC: [trunk/trunk]
2. Database: SQLite
3. Access layer: SQLDB / TSQLQuery / TSQLite3Connection
4. Platform: Linux x86_64

Thanks.

[BSaidus]

You should do this and it will work perfectelly

Code: SQL  [Select][+][-]

1. CREATE TABLE mytable (
2.   ...,
3.   timestamp_ms BIGINT NOT NULL,
4.   ...)
5. );
6.  

[kapibara]

Thats what I wanted to hear.

Thanks!

[Zvoni]

You should do this and it will work perfectelly

Code: SQL  [Select][+][-]

1. CREATE TABLE mytable (
2.   ...,
3.   timestamp_ms BIGINT NOT NULL,
4.   ...)
5. );
6.  

Yes and no.
Using "BIGINT" like shown still "only" assigns "Integer-Affinity", and sqlite itself will determine, which "size" to use depending on the magnitude of values
https://sqlite.org/datatype3.html

Storage Classes and Datatypes

Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:
    NULL. The value is a NULL value.
    INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
    REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
    TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
    BLOB. The value is a blob of data, stored exactly as it was input.

Your Problem is the DataType of the FieldDef, which gets set by the retrieving of the MetaData

In your case, you don't expect negative values, so you could even use "UNSIGNED BIG INT" in your Create Table.

OTOH, TSQLite3Connection has a Property "AlwaysUseBigInt"
Have a guess......

[Hartmut]

Because you have influence, how the database is created,

Code: SQL  [Select][+][-]

1. CREATE TABLE mytable (
2. ...
3.   timestamp_ms BIGINT NOT NULL,
4. ...
5.  

should be the easiest solution. Replacing BIGINT by LARGEINT should work the same.

But for people who want to read a foreign database, where they have no influence how the database was created, there are 2 more solutions possible:

Code: SQL  [Select][+][-]

1. SELECT CAST(timestamp_ms AS BIGINT) FROM mytable
2. -- which is much easier than
3. SELECT CAST(timestamp_ms AS TEXT) FROM mytable

And there was a bugfix in FPC 3.2.0 (revision 43802) which allows you to set

Code: Pascal  [Select][+][-]

1. var DBConnection1: TSQLite3Connection;
2. DBConnection1.AlwaysUseBigint:=true;

This allows you to read from databases, which have fields declared as INTEGER, to read them successfully with e.g. FieldByName('timestamp_ms').AsLargeInt.

[Zvoni]

Be aware:
AlwaysUseBigInt influences ALL Integer-Fields in a Query.
It doesn't matter which Integer-Type you declare your Columns with: This will always "expand" the FieldDef to 8 bytes

[Xenno]

It ignites my curiosity. Since SQLite natively uses the INTEGER storage class (which is effectively a 64-bit signed integer) for all integer values, does SQLDB parse the original CREATE TABLE statement (or query the schema) to determine the specific field type, allowing it to return the expected data type? What if it is a query especially more than one table, will it still work?

[Hartmut]

Be aware:
AlwaysUseBigInt influences ALL Integer-Fields in a Query.
It doesn't matter which Integer-Type you declare your Columns with: This will always "expand" the FieldDef to 8 bytes

Do you see any disadvantage by that? If you know, that a column fits in 32 bit, you can still read it by '.AsLongint'.

[Thaddy]

Once one of the Sqlite integer classes are in memory, they are as per the same documentation expanded to 8.

[Zvoni]

It ignites my curiosity. Since SQLite natively uses the INTEGER storage class (which is effectively a 64-bit signed integer) for all integer values, does SQLDB parse the original CREATE TABLE statement (or query the schema) to determine the specific field type, allowing it to return the expected data type? What if it is a query especially more than one table, will it still work?

No. It actually gets the Result-Datatype from SQLite itself (and maps it to fpc's enum)
(in sqlite3connection around line 580 +/-)

[Zvoni]

Be aware:
AlwaysUseBigInt influences ALL Integer-Fields in a Query.
It doesn't matter which Integer-Type you declare your Columns with: This will always "expand" the FieldDef to 8 bytes

Do you see any disadvantage by that? If you know, that a column fits in 32 bit, you can still read it by '.AsLongint'.

Not really. Except maybe "unneccessary" Memory-Usage

[Xenno]

No. It actually gets the Result-Datatype from SQLite itself (and maps it to fpc's enum)
(in sqlite3connection around line 580 +/-)

It's good, then. However, I found SQLDB has ExtractPrecisionAndScale function that uses below mapping:

Code: Pascal  [Select][+][-]

1.   FieldMap : Array [1..FieldMapCount] of TFieldMap = (
2.    (n:'INT'; t: ftInteger),
3.    (n:'LARGEINT'; t:ftLargeInt),
4.    (n:'BIGINT'; t:ftLargeInt),
5.    (n:'SMALLINT'; t: ftSmallint),
6.    (n:'TINYINT'; t: ftSmallint),
7.    (n:'WORD'; t: ftWord),
8.    (n:'BOOLEAN'; t: ftBoolean),
9.    ...

   
* Was the exclusion of INTEGER from the FieldMap intentional, or is it an oversight?

AFAIK, sqlite3 only provides 2 functions for reading integer values: sqlite3_column_int and sqlite3_column_int64. SQLDB's LoadField function uses them:   

• sqlite3_column_int for reading ftAutoInc, ftInteger, ftSmallInt, ftWord, ftBoolean
• sqlite3_column_int64 for reading ftLargeInt

This seems to pose a risk when sharing database files between applications. For an example, if another application (non-SQLDB) writes a 64-bit integer into an INTEGER column (not defined as BIGINT/LARGEINT), and my Lazarus application reads it as ftInteger, I lose the upper 32 bit. Should Integer fields in SQLite ideally be mapped to ftLargeInt by default to ensure 64-bit safety?

+1, I always treat them as Int64.

[Zvoni]

No. It actually gets the Result-Datatype from SQLite itself (and maps it to fpc's enum)
(in sqlite3connection around line 580 +/-)

It's good, then. However, I found SQLDB has ExtractPrecisionAndScale function that uses below mapping:

Code: Pascal  [Select][+][-]

1.   FieldMap : Array [1..FieldMapCount] of TFieldMap = (
2.    (n:'INT'; t: ftInteger),
3.    (n:'LARGEINT'; t:ftLargeInt),
4.    (n:'BIGINT'; t:ftLargeInt),
5.    (n:'SMALLINT'; t: ftSmallint),
6.    (n:'TINYINT'; t: ftSmallint),
7.    (n:'WORD'; t: ftWord),
8.    (n:'BOOLEAN'; t: ftBoolean),
9.    ...

   
* Was the exclusion of INTEGER from the FieldMap intentional, or is it an oversight?

AFAIK, sqlite3 only provides 2 functions for reading integer values: sqlite3_column_int and sqlite3_column_int64. SQLDB's LoadField function uses them:   

• sqlite3_column_int for reading ftAutoInc, ftInteger, ftSmallInt, ftWord, ftBoolean
• sqlite3_column_int64 for reading ftLargeInt

This seems to pose a risk when sharing database files between applications. For an example, if another application (non-SQLDB) writes a 64-bit integer into an INTEGER column (not defined as BIGINT/LARGEINT), and my Lazarus application reads it as ftInteger, I lose the upper 32 bit. Should Integer fields in SQLite ideally be mapped to ftLargeInt by default to ensure 64-bit safety?

+1, I always treat them as Int64.

Yes and no.

sqlite3connection, beginning at Line 520
it uses "sqlite3_column_decltype" to get the DataType-Name the Column was declared with (!!)
https://www.sqlite.org/c3ref/column_decltype.html

SQLite uses dynamic run-time typing. So just because a column is declared to contain a particular type does not mean that the data stored in that column is of the declared type. SQLite is strongly typed, but the typing is dynamic not static. Type is associated with individual values, not with the containers used to hold those values.

The check against the FieldMap is against that declared Type (it's a String).

If not Found (for whatever reason), it uses "sqlite3_column_type"
https://sqlite.org/c3ref/column_blob.html
which is the DataType of the Result (NOT THE DECLARED TYPE OF THE COLUMN), and based on the result, it assigns the Storage-Class, which assigns ftLargeInt to Integers-Results!!
Line 535 in sqlite3conn

[Hartmut]

This seems to pose a risk when sharing database files between applications. For an example, if another application (non-SQLDB) writes a 64-bit integer into an INTEGER column (not defined as BIGINT/LARGEINT), and my Lazarus application reads it as ftInteger, I lose the upper 32 bit. Should Integer fields in SQLite ideally be mapped to ftLargeInt by default to ensure 64-bit safety?

If a column of a SQLite-database is declared as INTEGER, then *every* application may store 64-bit values in this column.

If you write an application to read such a database and if you can't guarantee, that there will be never values > 32 bits in such a column, then from my understanding you *must* either
 - query all such columns with "SELECT CAST(columnname AS BIGINT) FROM tablename"
 - or use 'AlwaysUseBigint:=true'
(both described in reply #4). Otherwise you would receive wrong values, if a value is > 32 bits.

If you want examples and a demo for that please have a look in https://gitlab.com/freepascal.org/fpc/source/-/work_items/36486