SERIAL wrap-around

>From Informix Guide to SQL: Tutorial (Version 9.1) p.9-10: "The SERIAL data type can yield up to 2*31 - 1 positive integers... However, if all the positive serial numbers were used, the database server would continue to generate new numbers. It would treat the next serial quantity as a signed integer. Because the database server uses only positive values, it would simply wrap around and start to generate integer values that begin with a 1." I'm not sure what this means. Wrapping around a 32-bits signed integer means that the next value is the signed integer -(2*31). I don't know if the database server treats this as -(2*31) or just starts over and reuses serial numbers. If "uses only positive values" means ignoring the highest order bit, the next value after wraparound would be 0, not 1. Has anyone on the list tested serial wrap-around before? What did you find? -------------------------------------------------------- Peeter Pirn http://www.lfg.com

"Pirn, Peeter" wrote: > > >From Informix Guide to SQL: Tutorial (Version 9.1) p.9-10: > > "The SERIAL data type can yield up to 2*31 - 1 positive integers... > > However, if all the positive serial numbers were used, the database server > would continue to generate new numbers. It would treat > the next serial quantity as a signed integer. Because the database server > uses only positive values, it would simply wrap around and > start to generate integer values that begin with a 1." > > I'm not sure what this means. Wrapping around a 32-bits signed integer means > that the next > value is the signed integer -(2*31). I don't know if the database server > treats this as -(2*31) or just starts over and reuses > serial numbers. If "uses only positive values" means ignoring the highest > order bit, the next value after wraparound would be 0, > not 1. > > Has anyone on the list tested serial wrap-around before? Aeons ago. >What did you find? It wraps from 2^31 - 1 to 1, as stated. And the insert fails if record 1 still exists. And it used to be the case that a direct insert of 2^31 - 1 sent things dolally (but 2^31-2 was OK), but that bug has since been fixed. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

See for yourself! create table junk ( junk1 serial ); insert into junk values (3); insert into junk values (2147483647); insert into junk values (0); insert into junk values (0); insert into junk values (0); select * from junk; drop table junk; Note : A serial column is not automatically constrained to be unique. Rudy "Pirn, Peeter" wrote: > >From Informix Guide to SQL: Tutorial (Version 9.1) p.9-10: > > "The SERIAL data type can yield up to 2*31 - 1 positive integers... > > However, if all the positive serial numbers were used, the database server > would continue to generate new numbers. It would treat > the next serial quantity as a signed integer. Because the database server > uses only positive values, it would simply wrap around and > start to generate integer values that begin with a 1." > > I'm not sure what this means. Wrapping around a 32-bits signed integer means > that the next > value is the signed integer -(2*31). I don't know if the database server > treats this as -(2*31) or just starts over and reuses > serial numbers. If "uses only positive values" means ignoring the highest > order bit, the next value after wraparound would be 0, > not 1. > > Has anyone on the list tested serial wrap-around before? What did you find? > > -------------------------------------------------------- > Peeter Pirn http://www.lfg.com