Integer overflow risk in sqlite3_changes and underlying counter?
(1) By anonymous on 2021-06-21 17:32:14 [link] [source]
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:
int sqlite3_changes(sqlite3*)
and its underlying counter in sqlite3 struct:
int nChange
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.
(2) By anonymous on 2021-06-21 21:56:35 in reply to 1 [link] [source]
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.
(3) By Larry Brasfield (larrybr) on 2021-06-22 23:06:11 in reply to 2 [source]
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.
(4) By David Empson (dempson) on 2021-06-23 04:47:49 in reply to 3 [link] [source]
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.
(5.1) By Larry Brasfield (larrybr) on 2021-06-23 17:45:35 edited from 5.0 in reply to 4 [link] [source]
Your point is well taken and changes were made along the lines you suggest.