[SQLite] 64bit unsigned integer type support

[TilBlechschmidt]

It seems that sqlx currently has no support for unsigned 64bit integer values (u64). Especially for contents like file sizes, timestamps etc. this would be very favourable as SQLite has native support for these.

[mehcode]

What do you mean by SQLite has native support for them?

You can store an arbitrary 8-byte integer, yes, but if you try and manipulate it or compare it in SQL it will be signed operations.

[abonander]

SQLite has an unsigned 64-bit int typedef but no function exists to bind one as a parameter or retrieve one from a result row. The only use I could find in a cursory search is sqlute3_malloc64 and sqlite3_realloc64: https://www.sqlite.org/c3ref/free.html

[TilBlechschmidt]

I am not quite sure how the database does it internally but if you create a table with an INTEGER column you can store and interact with 64bit integers 🤷

 » sqlite3 /tmp/test.db
SQLite version 3.31.1 2020-02-01 00:08:28
Enter ".help" for usage hints.
sqlite> CREATE TABLE inttest (u INTEGER);
sqlite> INSERT INTO inttest VALUES (1099511627776);
sqlite> INSERT INTO inttest VALUES (1089511627776);
sqlite> SELECT SUM(u) FROM inttest;
2189023255552
sqlite>

As far as I can tell (correct me if I'm wrong) there is currently no way to store numbers like 1099511627776 (2^40) through the sqlx API.

[abonander]

You're using signed (not unsigned) 64-bit integers there which is what SQLite supports, and what we support via i64. We already have the requisite impls:

• impl Type<Sqlite> for i64
• impl Encode<Sqlite> for i64
• impl Decode<'a, Sqlite> for i64

If you're using INTEGER with query!() (the macro) it looks like it will always typecheck it as i32 by default because of the ordering here: https://github.com/launchbadge/sqlx/blob/v0.3.5/sqlx-macros/src/database/sqlite.rs#L4

@mehcode since it goes through in order and checks TypeInfo::compatible() then it will never choose i64 (or probably f64 for that matter). Should we change it to always choose i64 / f64?

[mehcode]

You're reading 0.3 @abonander , 0.4 ( master ) does auto select to i64.

---

@TilBlechschmidt So I poked SQLite. What is happening is SQLite ( because what are types really ) is seeing that your data is above 2^63 and shoving it into a NUMERIC column which is backed by double in C and can be read in SQLx using f64.

---

sqlite> CREATE TABLE foo ( num INT );

sqlite> INSERT INTO foo ( num ) VALUES ( 9223372036854775807 );
sqlite> SELECT * FROM foo;
9223372036854775807

sqlite> INSERT INTO foo ( num ) VALUES ( 9223372036854775808 );
sqlite> SELECT * FROM foo;
9223372036854775807
9.22337203685478e+18

sqlite> SELECT num, typeof(num) FROM foo;
9223372036854775807|integer
9.22337203685478e+18|real

---

If you're trying to read integers from 2^32 to 2^63, this is fixed on master and SQLx outputs i64 for INTEGER types in SQLite. I would refrain from trying to store values of 2^64 or more in SQLite directly due to the lossy nature of floating-point.

[mcronce]

@mehcode sorry to resurrect an old issue - is it also the case that BIGINT UNSIGNED columns with values >2^63 are shoved in a NUMERIC column?