Integer overflow risk in sqlite3_changes and underlying counter?
Is there an internal limit in SQLite that prevents more than INT_MAX rows being altered in a table?
The reason I worry, from a boundary case point of view, is that a table can clearly have more than INT_MAX rows (2147483647 on Intel x86) and yet the sqlite3 struct and some API calls around it track and return an "int" e.g., the function:
and its underlying counter in sqlite3 struct:
Can I assume that even in a huge table SQLite won't change more than INT_MAX rows? Or do I have to worry about an overflow 'wrap' giving me the wrong answer? If overflow is impossible, does SQLite truncate a SQL transaction at INT_MAX rows, or do I have to trap some specific error code to alert me to overflow being prevented by SQLite limiting things internally?
Thanks for the feedback.
sqlite> .changes on sqlite> select count(*) from foo; count(*) 2147483648 changes: 0 total_changes: 0 sqlite> delete from foo; changes: -2147483648 total_changes: -2147483648 sqlite>
So yes, the change count wraps around to negative on overflow.
Dan Kennedy has provided a remedy for this problem. Expect to see this in binaries next released, or your own if you build them from the VCS trunk.
Looking at the remedy, there seem to be some omissions which will continue to produce misleading results on 32-bit architectures when many changes have occurred.
In src/func.c, the SQL total_changes() function should be updated to call sqlite3_total_changes64() and return an int64. The SQL changes() function was updated so they are inconsistent as of this check-in.
In src/shell.c the ".changes on" display (implemented in the function runOneSqlLine) should be calling sqlite3_changes64() and sqlite3_total_changes64() and printing 64-bit integers.