SQLITE bug

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE bug

Noah Simon
Hello,
While using the sqlite3 SQL shell from macOS Sierra Terminal, a bug (I think) occurred.

Version number:
sqlite> .version
SQLite 3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2

What happened was I created a table with an autoincremented primary key. After creating a row of values, I deleted that row. I then created two more rows, but their IDs were 2 and 3, not 1 and 2. The expected behavior was that when I removed the most recent row in a table, the autoincrement would go back. Is this a misunderstanding on my part, or should this be fixed? Let me know if you need reproducing steps.

Thank you,
Noah Simon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE bug

Richard Hipp-3
On 8/31/17, Noah Simon <[hidden email]> wrote:

> Hello,
> While using the sqlite3 SQL shell from macOS Sierra Terminal, a bug (I
> think) occurred.
>
> Version number:
> sqlite> .version
> SQLite 3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2
>
> What happened was I created a table with an autoincremented primary key.
> After creating a row of values, I deleted that row. I then created two more
> rows, but their IDs were 2 and 3, not 1 and 2. The expected behavior was
> that when I removed the most recent row in a table, the autoincrement would
> go back.

Your expectations are incorrect.  See
https://www.sqlite.org/autoinc.html for complete documentation of
AUTOINCREMENT in SQLite.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE bug

R Smith
In reply to this post by Noah Simon
I think this has been answered already in that it is not a bug, but I
would like to mention why it works the way it works.

Imagine you have some tables, like a list of clients (table: client) and
a list of contacts which are foreign-keyed (or simply used in
conjunction) with the client parent table.

If you add 3 clients, let's assume they get IDs 1, 2 and 3 and then you
create for each of them 2 contacts which link to their parent via the
key so that contacts 1 and 2 has Company 1 as their parent, Contacts 3 &
4 has parent company 2, and so on.

Later you decide to delete Company 2. This leaves Contacts 3 and 4
"hanging", which may or may not be fine with you (and if the foreign key
was set up with cascading deletes then they would have been deleted too,
but let's imagine they are not). Now if you add the next company,
company 4, and it magically gets the same ID that was used before (ID 2)
then those hanging contacts will now again seem to be linked and they
will seem to be linked to company 4 which now has ID 2. Which is of
course complete unwanted.

You can extrapolate this to larger scale DBs where entire history tables
or comments or any amount of things can be linked to IDs which will
point to the wrong entries if IDs are re-used. The industry standard to
avoid any and all of this, is to NEVER use an ID twice.

In SQLite, there might be a way to achieve what you want/expected (more
or less).

If a table is created with an Integer primary key but without the
"AUTOINCREMENT" directive, it will still auto-increment the new keys if
you insert rows with NULL for the ID (because the DB helps out since the
primary key cannot be duplicate or null, if you try to insert null for
the PK, it assumes you mean "the next ID"). When you delete rows *from
the END of the list* it might use those deleted IDs again (i.e. if you
had ID 1, 2 and 3 and you deleted 2 and 3, it might use 2 and 3 again in
a next session, but if you deleted 2 from 1,2,3 it will still make the
next 4 so that you have 1,3,4 now - it won't assign an ID less than the
current last ID in the DB).

If you use the AUTOINCREMENT directive, SQLite promises to never use an
ID twice, so every next ID will be one higher than any ID that was
already used before, deleted or not (i.e. if you had IDs 1,2 and 3 and
deleted the last two, the next added one will be 4 regardless).

You can circumvent this behaviour by UPDATEing the sqlite3_sequence
table (which keeps a record of the last-assigned incremented ID for
every table) with the new lower ID to use (or whatever you like  the
next ID to be). I'm not sure now if it keeps the last ID used, or the
next ID to use, but that's a matter of public record or a 1-minute test
to figure out.

Lastly, a comment I've made possibly more than once on this list: There
is no imperative to trust the SQL engine with ID assignments. You are
free to (and I prefer to) assign IDs yourself. You can add ID n to the
list, you do not need in any way to be at the mercy of the "next ID"
decided by the SQL engine. AUTOINCREMENT is merely a convenience, not a
rule, requirement or standard (in conformance terms).


Hope that makes sense :)
Ryan


On 2017/09/01 3:20 AM, Noah Simon wrote:

> Hello,
> While using the sqlite3 SQL shell from macOS Sierra Terminal, a bug (I think) occurred.
>
> Version number:
> sqlite> .version
> SQLite 3.13.0 2016-05-18 10:57:30 fc49f556e48970561d7ab6a2f24fdd7d9eb81ff2
>
> What happened was I created a table with an autoincremented primary key. After creating a row of values, I deleted that row. I then created two more rows, but their IDs were 2 and 3, not 1 and 2. The expected behavior was that when I removed the most recent row in a table, the autoincrement would go back. Is this a misunderstanding on my part, or should this be fixed? Let me know if you need reproducing steps.
>
> Thank you,
> Noah Simon
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE bug

Joseph L. Casale
-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On
Behalf Of R Smith
Sent: Sunday, September 3, 2017 7:51 AM
To: [hidden email]
Subject: Re: [sqlite] SQLITE bug

> Lastly, a comment I've made possibly more than once on this list: There
> is no imperative to trust the SQL engine with ID assignments. You are
> free to (and I prefer to) assign IDs yourself.

What exactly do you feel you benefit by taking ownership of the ID, specifically
that of which you feel supersedes the obvious perils in the cases you noted?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE bug

Richard Damon
On 9/3/17 10:16 AM, Joseph L. Casale wrote:

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of R Smith
> Sent: Sunday, September 3, 2017 7:51 AM
> To: [hidden email]
> Subject: Re: [sqlite] SQLITE bug
>
>> Lastly, a comment I've made possibly more than once on this list: There
>> is no imperative to trust the SQL engine with ID assignments. You are
>> free to (and I prefer to) assign IDs yourself.
> What exactly do you feel you benefit by taking ownership of the ID, specifically
> that of which you feel supersedes the obvious perils in the cases you noted?
One BIG example of a place to overrule the default ID assignment via
auto-increment is in restoring a backup. Here, you NEED to ID to be the
same as before so Foreign keys in other tables stay pointing to the
right record.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE bug

R Smith
In reply to this post by Joseph L. Casale

On 2017/09/03 4:16 PM, Joseph L. Casale wrote:

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On
> Behalf Of R Smith
> Sent: Sunday, September 3, 2017 7:51 AM
> To: [hidden email]
> Subject: Re: [sqlite] SQLITE bug
>
>> Lastly, a comment I've made possibly more than once on this list: There
>> is no imperative to trust the SQL engine with ID assignments. You are
>> free to (and I prefer to) assign IDs yourself.
> What exactly do you feel you benefit by taking ownership of the ID, specifically
> that of which you feel supersedes the obvious perils in the cases you noted?

Several things, I'll restricted this to the top three because TLDR.

First and foremost, precise control. I can decide on a per-system basis
which IDs will be available. Now the use-case in SQLite terms is obvious
since every SQLite database is a unique system unto itself (no central
server) and I can control which IDs (or ID blocks) will be used. The use
case for data restoration or synchronization between keyed DBs is
obvious too, so I'll concentrate on the less obvious things. For one, I
can rest assured no tampering will break the systems when the PKs are
controlled (like some naughty software/person/bug adjusting the
sqlite3_sequence table or just adding a row that has an ID near the
64bit limit).

For a more non-SQLite reason, I can control distributed systems
specifying which ID-blocks are assigned by which system as a way to
control system-group-wide unique key IDs without resorting to GUIDs and
the like[1].  I can also check for non-DB based corruption (the DB
mechanism is fine but the data isn't working like it should because of
bugs, tampering or other faults) based on violation of the predefined
Key assignment ranges - at least as one of the checks.


The next item pertains to all SQL DBs. Lower linking complexity - this
is the most important, but a bit hard to explain. If you have multiple
linked tables (as one often does) then I can control the linked IDs
between them. A small example from a typical use case we have: We store
Addresses in a table (A single company might have more than one site and
address), and to every address is a linked row in another table with GPS
information (Lat, Long, google maps api links, etc.) and yet another
table holds cached maps image blobs and the like - so sometimes an ID is
used for an address which do not have any cached maps, but the next
address ID added will add the same ID for the GPS table and the Maps
table (often skipping a PK ID or two to achieve this). Also, later I can
add cached maps for any skipped item by simply inserting with its same ID.

The first thing to note about the above is that I can simply say in
pseudo code:
   i = calc_new_id();
   INSERT INTO Addresses(ID, V1, V2, ...) VALUES (:i, :P1, :P2, ....);
   INSERT INTO Maps(ID, V1, V2, ...) VALUES (:i, :P1, :P2, ....);
   INSERT INTO GPS(ID, V1, V2, ...) VALUES (:i, :P1, :P2, ....);

whereas in a AUTOINCREMENT based conventional approach might end up
something like:
   INSERT INTO Address(ID, V1, V2, ...) VALUES (NULL, :P1, :P2, ....);
   i = getLastInsertID();
   INSERT INTO Maps(ID, KeyToAddress, V1, V2 ...) VALUES (NULL, :i, :P1,
:P2, ....);
   m = getLastInsertID();
   INSERT INTO GPS(ID, KeyToAddress, KeyToMap, V1, V2 ...) VALUES (NULL,
:i, :m, :P1, :P2, ....);

with some possible variation depending on your needs.

Debugging systems like the first example above is much easier in human
terms since, when manually cross-checking, I don't have to remember 3
different IDs... The Address at ID 177 has Map data at ID 177 in the
Maps table and GPS data at ID 177 in the GPS table... you see the
pattern easily. It also obviates the need for an additional FK column in
the subsequent tables to "map" to the parent address, since the PK in
itself /IS/ the FK to the parent. (I might still add FKs to gain
cascading functionality or where the relation is one-to-many).

You control EVERY other piece of data you push into the DB, why not the
Key too? Or put differently, why would you rather do in principle:
   INSERT Stuff;
   Get the Key for it;
   Use the key to insert more stuff or use elsewhere if needed; [2]

Than:
   Make the Key;
   Use the key to insert stuff or use elsewhere if needed;

I know the "Make the Key" step might be a little bit of effort for quick
little DBs, so I too use the auto-increment for them, but for everything
substantial, the amount of coding to do this pales in comparison with
the amount of code you write to do normal system checking and testing,
AND, using your own specified keys can often save some code on the
checking side.

[Let me admit here that in many non-intrinsic applications my
"calc_new_id();" function often resolves to simply "SELECT MAX(ID)+1
FROM..." and a validation check, often no more than a 2-line function,
but in other systems it may get as complex as contacting a central
server to gain/verify the next pool of insert IDs for the local system
to use.]


Lastly - One small gripe I have with auto-increment is that it lulls
system programmers into wanting to make INT PK's for everything, even
stuff where that is clearly the wrong/unneeded approach. An Order-entry
system should have the full Order-Number as the PK, not an INT.  I often
see people making a table like this and just out of habit throwing an
INT PRIMARY KEY AUTOINCREMENT in there with the next column being the:
OrderNo TEXT COLLATE NOCASE UNIQUE - I ask you: if it walks like a PK,
and talks like a PK... isn't it the real PK?.

There is nothing inherently relational about integers, it's a computer
sourced convention used more for its ordinal properties than relational
properties. Nobody talks about person 44's children.. It's John's
children.   Of course it has merit on the point of storing the referring
INT in many other tables being more efficient than including the entire
TEXT value, and in some systems the lookup is faster (in MSSQL
definitely, but I have not tested it in SQLite). This is however an
ever-dwindling advantage.

Oh yes, and another reason: My OCD.  :)

Now just to be clear: I am not advocating the banning of AUTOINCREMENT,
just trying to point out that it's a hammer graciously provided by DB
engines, but no rule requires you to use it, and some jobs are not nails
- especially if you have an expectation of what the ID should be (like
the OP did).


Cheers,
Ryan


[1] I do actually use and suggest to use correctly calculated GUIDs for
system-wide Unique references which avoids ID blocks or pools, but, a
GUID is the most human-unfriendly ID possible and takes more space than
an INT, so I only resort to it when the scale of the system justifies,
but that's another debate and you can find discussion threads on it in
this very list.

[2] If you do not care at all what the Key is (non-linked tables where
you won't use the Key for anything else), then Auto-Increment is just
dandy, of course.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: SQLITE bug

Hick Gunter
In reply to this post by Richard Damon
Are you really proposing to restore just one or a selected set of tables from a backup of the database? What state does a transaction that touches one or more tables that are restored and one or more tables that aren't go to? It can't be "commited" because some data is not in the final state, and it can't be "rolled back" because some data is not in it's original state.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Richard Damon
Gesendet: Sonntag, 03. September 2017 19:13
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] SQLITE bug

On 9/3/17 10:16 AM, Joseph L. Casale wrote:

> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]] On Behalf Of R
> Smith
> Sent: Sunday, September 3, 2017 7:51 AM
> To: [hidden email]
> Subject: Re: [sqlite] SQLITE bug
>
>> Lastly, a comment I've made possibly more than once on this list:
>> There is no imperative to trust the SQL engine with ID assignments.
>> You are free to (and I prefer to) assign IDs yourself.
> What exactly do you feel you benefit by taking ownership of the ID,
> specifically that of which you feel supersedes the obvious perils in the cases you noted?
One BIG example of a place to overrule the default ID assignment via auto-increment is in restoring a backup. Here, you NEED to ID to be the same as before so Foreign keys in other tables stay pointing to the right record.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: SQLITE bug

Richard Damon
Depends on why you are restoring a backup (what data got corrupted).
Even if you are restoring a full backup of all the tables, you still
need to restore the ID fields rather than using the auto increment value
or you still break the relationships.

On 9/4/17 2:42 AM, Hick Gunter wrote:

> Are you really proposing to restore just one or a selected set of tables from a backup of the database? What state does a transaction that touches one or more tables that are restored and one or more tables that aren't go to? It can't be "commited" because some data is not in the final state, and it can't be "rolled back" because some data is not in it's original state.
>
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Richard Damon
> Gesendet: Sonntag, 03. September 2017 19:13
> An: [hidden email]
> Betreff: [EXTERNAL] Re: [sqlite] SQLITE bug
>
> On 9/3/17 10:16 AM, Joseph L. Casale wrote:
>> -----Original Message-----
>> From: sqlite-users
>> [mailto:[hidden email]] On Behalf Of R
>> Smith
>> Sent: Sunday, September 3, 2017 7:51 AM
>> To: [hidden email]
>> Subject: Re: [sqlite] SQLITE bug
>>
>>> Lastly, a comment I've made possibly more than once on this list:
>>> There is no imperative to trust the SQL engine with ID assignments.
>>> You are free to (and I prefer to) assign IDs yourself.
>> What exactly do you feel you benefit by taking ownership of the ID,
>> specifically that of which you feel supersedes the obvious perils in the cases you noted?
> One BIG example of a place to overrule the default ID assignment via auto-increment is in restoring a backup. Here, you NEED to ID to be the same as before so Foreign keys in other tables stay pointing to the right record.
>
> --
> Richard Damon
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users