SQLite Forum

Questions about data types and overflow bounds
Login

Questions about data types and overflow bounds

(1) By anonymous on 2021-08-05 12:50:54 [link]

Hello friends,

I'm wondering how SQLite work on handling integer overflow bounds, just like this:

```
CREATE TABLE v0(c0 INTEGER PRIMARY KEY);
CREATE TABLE v1(c1 INTEGER);
INSERT INTO v0 VALUES(9223372036854775807);
INSERT INTO v1 VALUES(9223372036854775807);
SELECT * FROM v0 WHERE v0.c0>=(9223372036854775807 + 1); --9223372036854775807 
SELECT * FROM v1 WHERE v1.c1>=(9223372036854775807 + 1); --nothing
```
First, we specify the use of PRIMARY KEY on v0, we can get a plan as follows:

> QUERY PLAN

> `--SEARCH v0 USING INTEGER PRIMARY KEY (rowid>?)

From this, we can get a non-empty resultset, which means that an integer overflow has occurred here.

Then, we specify no use of PRIMARY KEY on v1, we can get a plan as follows:

>QUERY PLAN

>`--SCAN v1

And we can get an empty resultset from this, which probably means that integer overflow is well handled.

I'm really curious if there is some handling strategy, like shifting 1 to the left side of the inequality sign to make it subtractive or something. If so, did SQLite forget to apply the strategy to `PRIMARY KEY`?

Looking forward to your reply!

(2.1) By Simon Slavin (slavin) on 2021-08-07 13:30:43 edited from 2.0 in reply to 1

> If so, did SQLite forget to apply the strategy to PRIMARY KEY?

Not 'forget' as much as 'not care'.  Values for INTEGER PRIMARY KEY fields are processed to make them useful for primary keys without the programmer having to deal with special cases.  SQLite can do anything up to and including picking a random integer, as long as it keeps the column suitable for use as a primary key.

<https://sqlite.org/faq.html#q1>

If you want fine-detail control over the values in an INTEGER key column, don't define it as a INTEGER PRIMARY KEY, define it as INTEGER UNIQUE and set the values you want.