So in short when inserting huge integers to sqlite3 database like example 302140846680178689 it ends up saved as 302140846680178700 and I don't know why sometimes the last 1-2 digits are incorrect.
I say sometimes because it isn't consistent. For example 197918569894379520 will remain 197918569894379520.
Python code to recreate (I'm using 3.7.6 64bit):
import sqlite3
conn = sqlite3.connect("example.db")
some_id = 302140846680178689
c = conn.cursor()
c.execute("CREATE TABLE example(one TEXT, two INTEGER, three BIG INT)")
c.execute("INSERT INTO example VALUES (?,?,?)", (some_id, some_id, some_id))
conn.commit()
conn.close()
The resulting table looks like:
+--------------------+--------------------+--------------------+
| one(TEXT) | two(INTEGER) | three(BIG INT) |
+--------------------+--------------------+--------------------+
| 302140846680178689 | 302140846680178700 | 302140846680178700 |
+--------------------+--------------------+--------------------+
I expected same result for integer and big int as per point 3.1.1. of the docs it seems big int is just another name for integer and it will be integer regardless? Sqlite will internally manage the size as quote INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
So the maximum size for integer seems to be 2^63-1 which is big enough to fit 302140846680178689 and it also doesn't seem to be some rounding error from the module as print(int(float(302140846680178689)) - 302140846680178689) gives -1 and yet I have difference of 11.
Update: Giving more info as per questions in comments
print(sqlite3.version)
> 2.6.0
print(sqlite3.sqlite_version)
> 3.28.0
c.execute("SELECT typeof(two) FROM example;")
print(c.fetchall())
> [('integer',)]
Adding this at end of script:
print(type(some_id))
> <class 'int'>
