> It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT"
What I would want, ...expect, is that a primary key autoincrement column would be left completely alone. And if was altered, it was altered on accident. I always thought "integer primary key" was synonymous with __rowid__. What application would want to mess with that? On Mon, Oct 31, 2016 at 12:56 PM, Richard Hipp <d...@sqlite.org> wrote: > On 10/27/16, Adam Goldman <ad...@pobox.com> wrote: > > I expected the test case below to print 5679, but it prints 1235 > > instead. I tested under a few versions including 3.15.0. It's a bit of a > > corner case and I worked around it in my application, but I guess it's a > > bug. > > > > CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); > > INSERT INTO foo (bar) VALUES(1234); > > UPDATE foo SET bar=5678; > > DELETE FROM foo; > > INSERT INTO foo DEFAULT VALUES; > > SELECT * FROM foo; > > Yes, this is a discrepancy between the implementation and the > documentation. But the implementation of AUTOINCREMENT has *never* > before modified the content of the sqlite_sequence table on an UPDATE, > since the AUTOINCREMENT feature was first introduced in 2002 (SQLite > version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it > does in 3.15.0. If we "fix" the implementation now, we run a risk of > breaking some of the millions of applications that use AUTOINCREMENT. > For that reason, we have tentatively decided to update the > documentation rather than the code. > > Note that the implied purpose of AUTOINCREMENT is to generate a unique > and immutable identifier. Running an UPDATE on an immutable > identifier breaks the contract. Even though this contract was not > previously stated in the documentation, apparently most people > understood it because this issue has never come up before in 14 years > of heavy use. > > Thanks for pointing out the problem. > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
