Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Keith Medcalf]

This has been the case since Microsoft stole SQL Server from Sybase (where it 
is/was the case as well).

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of James K. Lowden
> Sent: Thursday, 3 November, 2016 19:50
> To: SQLite mailing list
> Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE
> 
> On Tue, 1 Nov 2016 11:01:24 +
> Simon Slavin <slav...@bigfraud.org> wrote:
> 
> > attempts to change a value in that column using UPDATE always
> > generate an error.  I didn't know that.  I looked it up.  Apparently
> > Microsoft's SQLSERVER blocks it
> 
> Blocks but does not prevent.
> https://msdn.microsoft.com/en-us/library/ms188059.aspx
> 
> --jkl
> ___
> 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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[James K. Lowden]

On Tue, 1 Nov 2016 11:01:24 +
Simon Slavin  wrote:

> attempts to change a value in that column using UPDATE always
> generate an error.  I didn't know that.  I looked it up.  Apparently
> Microsoft's SQLSERVER blocks it

Blocks but does not prevent.
https://msdn.microsoft.com/en-us/library/ms188059.aspx

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[David Raymond]

sqlite> create table tbl1
   ...> (
   ...>   id integer primary key autoincrement,
   ...>   someOtherfield,
   ...>   yetAnotherField
   ...> );

sqlite> create trigger trg_imInChargeAndSayNoAutoincrementUpdates
   ...> before update of id on tbl1
   ...> begin
   ...>   select raise(abort, 'Bad dog, no biscuit.');
   ...> end;

sqlite> insert into tbl1 (someOtherField, yetAnotherField) values (1, 2);

sqlite> select * from tbl1;
id|someField|someOtherField
1|1|2

sqlite> update tbl1 set id = 2 where id = 1;
Error: Bad dog, no biscuit.






sqlite> create table tbl2
   ...> (
   ...>   id integer primary key autoincrement,
   ...>   someOtherField,
   ...>   yetAnotherField
   ...> );

sqlite> create trigger trg_imInChargeAndWantToUpdateMyAutoincrementFields
   ...> after update of id on tbl2
   ...> when new.id > (select seq from sqlite_sequence where name = 'tbl2')
   ...> begin
   ...>   update sqlite_sequence
   ...>   set seq = new.id where name = 'tbl2';
   ...> end;

sqlite> insert into tbl2 (someOtherField, yetAnotherField) values (1, 2);

sqlite> select * from tbl2;
id|someOtherField|yetAnotherField
1|1|2

sqlite> select * from sqlite_sequence;
name|seq
tbl1|1
tbl2|1

sqlite> update tbl2 set id = 5 where id = 1;

sqlite> select * from tbl2;
id|someOtherField|yetAnotherField
5|1|2

sqlite> select * from sqlite_sequence;
name|seq
tbl1|1
tbl2|5
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

Interesting. I am using an old version, 5.5.34

The database engine may also make a difference. I was using MyISAM. INNODB may 
be different.

A quick scan of the MySQL documentation suggests INNODB is different. For 
example, it doesn't
store the next value in the database.


If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle 
in the InnoDB data dictionary contains a special counter called the 
auto-increment counter that is used in assigning new values for the column. 
This counter is stored only in main memory, not on disk.

To initialize an auto-increment counter after a server restart, InnoDB executes 
the equivalent of the following statement on the first insert into a table 
containing an AUTO_INCREMENT column.

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

Simon's requested table is likely to get quite complicated :^)

I suspect all you can really say about auto increment is that it will create a 
unique number.


Andy


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Tue 01 November 2016 13:09
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE

Well, I got different results (so maybe it's version related).  I tried it 
on MySQL v5.7.16-log:
++--+
| id | v|
++--+
|  2 | two  |
| 10 | one  |
++--+
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
++---+
| id | v |
++---+
|  3 | three |
| 10 | one   |
| 30 | two   |
++---+

-Original Message- 
From: Simon Slavin
Sent: Tuesday, November 01, 2016 1:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:44am, Andy Ling <andy.l...@s-a-m.com> wrote:

> It remembers..

Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table. 
Apparently a single value for the table's primary key rather than a value 
for each "INTEGER PRIMARY KEY AUTOINCREMENT" column.

I'd be interested in the equivalents for progresql and Oracle, if anyone is 
set up to find out.  Maybe we could work up a table like the one in

<https://www.sqlite.org/nulls.html>

Simon.
___
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
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Tony Papadimitriou]

Well, I got different results (so maybe it's version related).  I tried it 
on MySQL v5.7.16-log:

++--+
| id | v|
++--+
|  2 | two  |
| 10 | one  |
++--+
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
++---+
| id | v |
++---+
|  3 | three |
| 10 | one   |
| 30 | two   |
++---+

-Original Message- 
From: Simon Slavin

Sent: Tuesday, November 01, 2016 1:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:44am, Andy Ling <andy.l...@s-a-m.com> wrote:


It remembers..


Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table. 
Apparently a single value for the table's primary key rather than a value 
for each "INTEGER PRIMARY KEY AUTOINCREMENT" column.


I'd be interested in the equivalents for progresql and Oracle, if anyone is 
set up to find out.  Maybe we could work up a table like the one in


<https://www.sqlite.org/nulls.html>

Simon.
___
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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

>> It remembers..

>Ah, neat.  Thanks for the testing.

As a slight aside. It also never resets the value. We had a problem where the 
number of inserts
had incremented the AUTO INCREMENT value to MAXINT (it took a few years). It 
then stops.
We fixed it by changing id to a BIGINT, but you can also reset the auto 
increment value via SQL.

Regards

Andy
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 11:44am, Andy Ling  wrote:

> It remembers..

Ah, neat.  Thanks for the testing.  And the "show create table" command you 
used makes it clear that the engine keeps a record for the table.  Apparently a 
single value for the table's primary key rather than a value for each "INTEGER 
PRIMARY KEY AUTOINCREMENT" column.

I'd be interested in the equivalents for progresql and Oracle, if anyone is set 
up to find out.  Maybe we could work up a table like the one in



Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

It remembers..

mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY 
KEY(id)) ;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> update tt set id=10 where v="one";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tt (v) VALUES("two");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 11 | two  |
++--+
2 rows in set (0.00 sec)

mysql> update tt set id=40 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 40 | two  |
++--+
2 rows in set (0.00 sec)

mysql> update tt set id=30 where v="two";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into tt (v) VALUES("three");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++---+
| id | v |
++---+
| 10 | one   |
| 30 | two   |
| 41 | three |
++---+
3 rows in set (0.00 sec)

After doing this fiddling, show create table shows the auto increment value

mysql> show create table tt;
+---+-
| Table | Create Table
+---+-
| tt| CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `v` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 |
+---+-
1 row in set (0.00 sec)





-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tue 01 November 2016 11:42
To: SQLite mailing list
Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE


On 1 Nov 2016, at 11:14am, Andy Ling <andy.l...@s-a-m.com> wrote:

> MySQL lets you fiddle.

I don't have MySQL.  To satisfy my curiosity, could you try this:

create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ;
insert into tt (v) VALUES("one");
update tt set id=10 where v="one";
insert into tt (v) VALUES("two");
select * from tt;

You got this far already (though I changed the details a little).

Now add this:

update tt set id=40 where v="two";
select * from tt;
update tt set id=30 where v="two";
insert into tt (v) VALUES("three");
select * from tt;

I'm trying to find out whether it remembers that there once was a value that 
was 40, even if the highest value present when you do the insert is 30.  My 
guess is that it'll assign an id of 41 to row three, but I might be wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[R Smith]

On 2016/11/01 1:01 PM, Simon Slavin wrote:

On 1 Nov 2016, at 10:45am, R Smith  wrote:


  D - Horrible if you up some key value significantly and then update it back 
down, because there is no way the Autoinc value should *EVER* be able/allowed 
to come back down. It's a one-way street.

This is an additional argument for not allowing UPDATE to change an "INTEGER PRIMARY 
KEY AUTOINCREMENT" value.


Well, either that, or an argument for not letting UPDATE dictate the 
next incremental key value. While I think this argument carries equal 
merit both ways (perhaps leaning more towards your suggestion), the 
consequence of disallowing a column to be updated is too great a 
disadvantage in my opinion (Even if it didn't break backwards 
compatibility).




Which, according to GB, is what some other SQL engines do: attempts to change a 
value in that column using UPDATE always generate an error.  I didn't know 
that.  I looked it up.  Apparently Microsoft's SQLSERVER blocks it, but I was 
unable to find anything mentioning how any of the other big SQL engines handles 
it.


Indeed, though MSSQL maintains what it calls "Identity" columns, which 
(IIUC) is like Autoincrement Primary Key (but need not be the official 
PK) that cannot be Updated. I don't know enough of it as I tend to avoid 
it when using MSSQL. MySQL definitely allows updates to Autoinc columns, 
and even updates the next key. Not sure about PostGres, but it is 
clearly not a blanket assumption that can be made. Seems everybody has 
their own take, which is ok, it just emphasizes what I tried to point 
out previously - This AUTOINC thing is a luxury item, not a mandated 
one, and one best avoided when you can roll your own.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 11:14am, Andy Ling  wrote:

> MySQL lets you fiddle.

I don't have MySQL.  To satisfy my curiosity, could you try this:

create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ;
insert into tt (v) VALUES("one");
update tt set id=10 where v="one";
insert into tt (v) VALUES("two");
select * from tt;

You got this far already (though I changed the details a little).

Now add this:

update tt set id=40 where v="two";
select * from tt;
update tt set id=30 where v="two";
insert into tt (v) VALUES("three");
select * from tt;

I'm trying to find out whether it remembers that there once was a value that 
was 40, even if the highest value present when you do the insert is 30.  My 
guess is that it'll assign an id of 41 to row three, but I might be wrong.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Andy Ling]

> Which, according to GB, is what some other SQL engines do: attempts to change 
> a value
>  in that column using UPDATE always generate an error.  I didn't know that.  
> I looked it up.
>   Apparently Microsoft's SQLSERVER blocks it, but I was unable to find 
> anything mentioning
>  how any of the other big SQL engines handles it.

MySQL lets you fiddle.

mysql> create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY 
KEY(id)) ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
|  1 | one  |
++--+
1 row in set (0.00 sec)

mysql> update tt set id=10 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
++--+
1 row in set (0.00 sec)

mysql> insert into tt (v) VALUES("one");
Query OK, 1 row affected (0.00 sec)

mysql> select * from tt;
++--+
| id | v|
++--+
| 10 | one  |
| 11 | one  |
++--+
2 rows in set (0.00 sec)
---
This email has been scanned for email related threats and delivered safely by 
Mimecast.
For more information please visit http://www.mimecast.com
---

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Richard Damon]

On 11/1/16 7:01 AM, Simon Slavin wrote:

On 1 Nov 2016, at 10:45am, R Smith  wrote:


  D - Horrible if you up some key value significantly and then update it back 
down, because there is no way the Autoinc value should *EVER* be able/allowed 
to come back down. It's a one-way street.

This is an additional argument for not allowing UPDATE to change an "INTEGER PRIMARY 
KEY AUTOINCREMENT" value.

Which, according to GB, is what some other SQL engines do: attempts to change a 
value in that column using UPDATE always generate an error.  I didn't know 
that.  I looked it up.  Apparently Microsoft's SQLSERVER blocks it, but I was 
unable to find anything mentioning how any of the other big SQL engines handles 
it.

Simon.
MySQL sets the increment to max+1 ever used, and uses the auto=increment 
value any time you try to set the field to 0 (other values get written 
at provided, assuming they are unique). This can be handy when you 
create the table schema and then restore data that was saved from a 
previous version of the table, The restored data has values for the 
field, which you want to be used, and may not use every number as some 
records might have been deleted. If it strictly just used the 
auto-increment value then any use of that as a foreign key in some other 
table gets broken.


--
Richard Damon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 10:45am, R Smith  wrote:

>  D - Horrible if you up some key value significantly and then update it back 
> down, because there is no way the Autoinc value should *EVER* be able/allowed 
> to come back down. It's a one-way street.

This is an additional argument for not allowing UPDATE to change an "INTEGER 
PRIMARY KEY AUTOINCREMENT" value.

Which, according to GB, is what some other SQL engines do: attempts to change a 
value in that column using UPDATE always generate an error.  I didn't know 
that.  I looked it up.  Apparently Microsoft's SQLSERVER blocks it, but I was 
unable to find anything mentioning how any of the other big SQL engines handles 
it.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[R Smith]

On 2016/11/01 11:52 AM, Simon Slavin wrote:


Unfortunately the response is going to be along the lines of "We can't do this for 
compatibility reasons because there might be a program out there that does it.".


And rightly so...

There is no reason to use Autoincrement other to be sure newly added 
items (in consecutive inserts) get an automatic next number as an 
integer primary key that was never used before in an insert. I have been 
vocal on here about how that should not really be left up to the DB 
engine, you should really assign your own numbering, but, for easiness 
sake, using the DB is fine.


Then, someone starts expecting miracles from the mechanism, which it may 
provide by virtue of doing some fun things at a cost, and then someone 
starts wanting it to "Lock down" the field because now the fun things 
shouldn't always happen because


I for one have many systems which update the Autoinc key, such as 
inserting a new value in between two others and the like. It would break 
bad if it locks up. I also do not wish the autoinc value to update 
during updates, because I may alter all of them in a single transaction 
and then back again after some shuffling and the like, at the end of 
which the Autoinc next value should still be what it was before the 
updates - not to mention saving valuable CPU cycles. On the occasion 
that I DO need it to update, amending the sequence table that keeps the 
next autoinc value is a simple thing to do.


Moral of the story: Don't use Autoincrement, and if you /have/ to - 
control it well and test it well, and rest assured that updates won't 
affect the next key value - unless you want to, in which case you can 
simply pop the MAX(...)+1 into the autoinc keys control table 
(sqlite_sequence).


An alternative would be to have UPDATE actually write the sequence 
table, and then issue another update to it if you /didn't/ want the next 
key to change - but that is:

  A - Contrary to how it currently works (backwards compatibility etc.),
  B - Not a huge audience for this method (14+ years of not a single 
such a request),

  C - Heavier on CPU cycles, and
  D - Horrible if you up some key value significantly and then update 
it back down, because there is no way the Autoinc value should *EVER* be 
able/allowed to come back down. It's a one-way street.



cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

On 1 Nov 2016, at 6:56am, GB  wrote:

> And that is why several SQL Engines prevent AUTOINCREMENT columns from being 
> UPDATEd.

I think that would be a good fix in SQLite's case.  If you're relying on SQLite 
to generate these unique numbers for you then you shouldn't then change your 
mind and try to correct one of its attempts.

Unfortunately the response is going to be along the lines of "We can't do this 
for compatibility reasons because there might be a program out there that does 
it.".  Roll on SQLite 4 in which this argument cannot apply.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Niall O'Reilly]

On 28 Oct 2016, at 12:47, Simon Slavin wrote:

It guess it comes down to what one wants from "INTEGER PRIMARY KEY 
AUTOINCREMENT". If the requirement is only-ever-increasing then this 
is a bug.


  The behaviour described at https://sqlite.org/autoinc.html seems to 
match this requirement:


  If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a 
slightly different
  ROWID selection algorithm is used. The ROWID chosen for the new row 
is at least one
  larger than the largest ROWID that has ever before existed in that 
same table.


  It looks to me as if there is a bug, and I'ld prefer to see the 
behaviour reported by

  the OP as buggy, rather than what is documented.

  Best regards,
  Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[GB]

And that is why several SQL Engines prevent AUTOINCREMENT columns from 
being UPDATEd.


I think it should be fixed in the Docs, not in Code.


Richard Hipp schrieb am 31.10.2016 um 18:56:


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.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Don V Nielsen]

> 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  wrote:

> On 10/27/16, Adam Goldman  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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Richard Hipp]

On 10/27/16, Adam Goldman  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

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Simon Slavin]

SQLite version 3.14.0 2016-07-26 15:17:14
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> INSERT INTO foo (bar) VALUES(1234);
sqlite> SELECT * FROM foo;
1234
sqlite> UPDATE foo SET bar=5678;
sqlite> SELECT * FROM foo;
5678
sqlite> DELETE FROM foo;
sqlite> SELECT * FROM foo;
sqlite> INSERT INTO foo DEFAULT VALUES;
sqlite> SELECT * FROM foo;
1235
sqlite> 

Suggests that in 3.14.0 the autoincrement number isn't changed by UPDATE.

It guess it comes down to what one wants from "INTEGER PRIMARY KEY 
AUTOINCREMENT". If the requirement is only-ever-increasing then this is a bug.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Stephen Chrzanowski]

Works here;

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> INSERT INTO foo (bar) VALUES(1234);
sqlite> SELECT * FROM foo;
1234
sqlite> UPDATE foo SET bar=5678;
sqlite> SELECT * FROM foo;
5678
sqlite> DELETE FROM foo;
sqlite> SELECT * FROM foo;
sqlite> INSERT INTO foo DEFAULT VALUES;
sqlite> SELECT * FROM foo;

And in one transaction;
sqlite> begin;
sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT);
sqlite> INSERT INTO foo (bar) VALUES(1234);
sqlite> SELECT * FROM foo;
1234
sqlite> UPDATE foo SET bar=5678;
sqlite> SELECT * FROM foo;
5678
sqlite> DELETE FROM foo;
sqlite> SELECT * FROM foo;
sqlite> INSERT INTO foo DEFAULT VALUES;
sqlite> SELECT * FROM foo;
1235
sqlite> commit;


On Fri, Oct 28, 2016 at 2:46 AM, Radovan Antloga 
wrote:

> After line:
> UPDATE foo SET bar=5678;
>
> put this sql command:
> COMMIT;
>
> If you execute all statements in one sql
> (except last), they are executed in one transaction.
>
> Regards
> Radovan
>
>
> Adam Goldman je 27.10.2016 ob 11:52 napisal:
>
> Hi,
>>
>> 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;
>>
>> -- Adam
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[Radovan Antloga]

After line:
UPDATE foo SET bar=5678;

put this sql command:
COMMIT;

If you execute all statements in one sql
(except last), they are executed in one transaction.

Regards
Radovan


Adam Goldman je 27.10.2016 ob 11:52 napisal:

Hi,

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;

-- Adam
___
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