SQLite Forum

Integer overflow risk in sqlite3_changes and underlying counter?

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;
    changes:   0   total_changes: 0
    sqlite> delete from foo;
    changes: -2147483648   total_changes: -2147483648

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.

  1. 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.

  2. 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.