Hi!
>>>>> "btjones" == btjones <btjones@stripped> writes:
btjones> I've been thinking on this one for awhile. I noted a while back that
btjones> auto-increment gets confused if you insert a negative number into the
btjones> auto-increment field, and this has been reported as expected behavior.
btjones> My thoughts are there are two possible solutions to this problem --
btjones> 1. Make all auto_increment columns be assumed unsigned by the silent
btjones> column change routines.
This would be the easy thing to do.
btjones> 2. While it is technically bad practice, I've observed it to be
btjones> relatively common practice to use negative numbers in an auto-incrementing
btjones> primary key column to designate "special" items, such as items that were
btjones> manually added alongside an automated process or to designate data that is
btjones> "display restricted" and easily separated with a simple WHERE
btjones> (primary_key_column > 0).
btjones> Looking in the source code, I found this section in handler.cc. Is
btjones> this the only relevant section for updating the auto_increment value stored
btjones> in the table itself? Does this handle all the various table types? If so,
btjones> would a patch be out of the question? It would seem you'd just need to
btjones> teach this routine to ignore negative numbers. I think this would be an
btjones> ideal feature for mySQL.
btjones> void handler::update_auto_increment()
<cut>
There is a little more that happens behind the scenes. The most
important one is probably that some handlers, at least MyISAM,
automaticly adjust the next-to-be-used auto_increment value for
each insert and update.
The main problem is that on this level the handler doesn't know if the
value is signed or not.
Another problem is that I don't know of any database that has a
similar feature like 'auto_increment' that has a special case for
negative numbers. I haven't really decided if it's even a good idea
to try to support negative numbers for the auto_increment column. I
haven't yet seen a application where it would be critical to have this
feature; If we would have this feature it would be nice to first try
to write a full specification of how this would work during all
possible circumstances, like what would happen if you try to insert
an auto_increment value of -1.
Regards,
Monty
