Quantcast

AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

Keith Medcalf

sqlite> create table x (key integer primary key, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=10000 where value='test';
sqlite> select * from x;
10000|test
sqlite> delete from x;
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
1|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=10000 where value='test';
sqlite> select * from x;
10000|test
sqlite> delete from x;
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
2|again
sqlite> drop table x;
sqlite> create table x (key integer primary key autoincrement, value text);
sqlite> insert into x values (null, 'test');
sqlite> update x set key=10000 where value='test';
sqlite> select * from x;
10000|test
sqlite> insert into x values (null, 'again');
sqlite> select * from x;
10000|test
10001|again
sqlite>

Question is:  when the rowid alias is declared autoincrement, Should the "update" update the hi-level mark for the key field?

---
Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
 -- Hunter S. Thompson




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

Re: AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

Richard Hipp-3
The behavior is correct.

I have adjusted the documentation to try to avoid ambiguity.  See
https://www.sqlite.org/docsrc/info/f6e2eab4e71644b1 for the
documentation update.

On 2/2/17, Keith Medcalf <[hidden email]> wrote:

>
> sqlite> create table x (key integer primary key, value text);
> sqlite> insert into x values (null, 'test');
> sqlite> update x set key=10000 where value='test';
> sqlite> select * from x;
> 10000|test
> sqlite> delete from x;
> sqlite> insert into x values (null, 'again');
> sqlite> select * from x;
> 1|again
> sqlite> drop table x;
> sqlite> create table x (key integer primary key autoincrement, value text);
> sqlite> insert into x values (null, 'test');
> sqlite> update x set key=10000 where value='test';
> sqlite> select * from x;
> 10000|test
> sqlite> delete from x;
> sqlite> insert into x values (null, 'again');
> sqlite> select * from x;
> 2|again
> sqlite> drop table x;
> sqlite> create table x (key integer primary key autoincrement, value text);
> sqlite> insert into x values (null, 'test');
> sqlite> update x set key=10000 where value='test';
> sqlite> select * from x;
> 10000|test
> sqlite> insert into x values (null, 'again');
> sqlite> select * from x;
> 10000|test
> 10001|again
> sqlite>
>
> Question is:  when the rowid alias is declared autoincrement, Should the
> "update" update the hi-level mark for the key field?
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
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
|  
Report Content as Inappropriate

Re: AUTOINCREMENT hi-level not updated when rowid updated -- Correct Behaviour?

Keith Medcalf


On Thursday, 2 February, 2017 18:56, Richard Hipp <[hidden email]> wrote:


> The behavior is correct.
 
> I have adjusted the documentation to try to avoid ambiguity.  See
> https://www.sqlite.org/docsrc/info/f6e2eab4e71644b1 for the
> documentation update.

The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before been inserted in that same table.

would perhaps something like:

The ROWID chosen for the new row is at least one larger than the largest ROWID
that has ever before been inserted, or currently exists, in that same table.

be more accurate since newrowid = max(1, largestinserted, max(rowid)) + 1 ?

> On 2/2/17, Keith Medcalf <[hidden email]> wrote:
> >
> > sqlite> create table x (key integer primary key, value text);
> > sqlite> insert into x values (null, 'test');
> > sqlite> update x set key=10000 where value='test';
> > sqlite> select * from x;
> > 10000|test
> > sqlite> delete from x;
> > sqlite> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 1|again
> > sqlite> drop table x;
> > sqlite> create table x (key integer primary key autoincrement, value
> text);
> > sqlite> insert into x values (null, 'test');
> > sqlite> update x set key=10000 where value='test';
> > sqlite> select * from x;
> > 10000|test
> > sqlite> delete from x;
> > sqlite> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 2|again
> > sqlite> drop table x;
> > sqlite> create table x (key integer primary key autoincrement, value
> text);
> > sqlite> insert into x values (null, 'test');
> > sqlite> update x set key=10000 where value='test';
> > sqlite> select * from x;
> > 10000|test
> > sqlite> insert into x values (null, 'again');
> > sqlite> select * from x;
> > 10000|test
> > 10001|again
> > sqlite>
> >
> > Question is:  when the rowid alias is declared autoincrement, Should the
> > "update" update the hi-level mark for the key field?
> >
> > ---
> > Life should not be a journey to the grave with the intention of arriving
> > safely in a pretty and well preserved body, but rather to skid in
> broadside
> > in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> > proclaiming "Wow! What a Ride!"
> >  -- Hunter S. Thompson
> >
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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
Loading...