BUG or strange behaviour of update on primary key

Hello there,
two guys of our developer team ( Lorenzo and Federico )
have seen a strange behaviour (in 8.4 and 9.1.1 ) on update, and I think is
a bug or something really strange or I not understand correctly this
behavior .

I explain now ( begin transaction or auto commit is the same):

create table testup ( a int ) ;

alter table testup add primary key (a ) ;

insert into testup values (1);

insert into testup values (2);

update testup set a=a+1 ;
ERROR: duplicate key value violates unique constraint "testup_pkey"
DETTAGLI: Key (a)=(2) already exists.

by the way :

test=# update testup set a=a-1 ;
UPDATE 2
SUCCESFUL

-- REVERSE ORDER --

Now create the same table with rows in reverse physical order:

create table testup2 ( a int ) ;

alter table testup2 add primary key (a ) ;

insert into testup2 values (2) ;

insert into testup2 values (1);

update testup2 set a=a+1 ;
UPDATE 2
SUCCESFUL

by the way :

test=# update testup2 set a=a-1 ;
ERROR: duplicate key value violates unique constraint "testup2_pkey"
DETTAGLI: Key (a)=(1) already exists.

I have tested in Oracle 11gR1 and 11gR2 without the same behaviour :

Oracle :

SQL> create table a ( b number ) ;

Tabella creata.

SQL> alter table a add primary key (b) ;

Tabella modificata.

SQL> insert into a values (1 ) ;

Creata 1 riga.

SQL> insert into a values (2) ;

Creata 1 riga.

SQL> commit ;

Commit completato.

SQL> update a set b=b+1 ;

Aggiornate 2 righe.

SQL> commit ;

Commit completato.

SQL> update a set b=b-1;

Aggiornate 2 righe.

SQL> commit;

Commit completato.

In MySQL 5.1.58 with InnoDB the behaviour is more strange (always for +1
and indipendent from the reverse order O_o) :

mysql> create table testup ( a int ) engine innodb ;
Query OK, 0 rows affected (0.21 sec)

mysql> alter table testup add primary key (a) ;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into testup values (1) ;
Query OK, 1 row affected (0.12 sec)

mysql> insert into testup values (2) ;
Query OK, 1 row affected (0.15 sec)

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql> update testup set a=a+1 ;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' (-- like
postgres!)
mysql> update testup set a=a-1 ;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql> update testup set a=a+1 ;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from testup ;
+---+
| a |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)

-- REVERSE ORDER --

mysql> truncate table testup ;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into testup values (2) ;
Query OK, 1 row affected (0.12 sec)

mysql> insert into testup values (1) ;
Query OK, 1 row affected (0.17 sec)

mysql> update testup set a=a+1 ; (-- O_O is tottaly different from
postgres!)
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> update testup set a=a-1 ;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 2 Changed: 2 Warnings: 0

In MySql with Myisam is tottaly different and similar to Oracle :

mysql> create table testup_myisam ( a int ) engine myisam ;
Query OK, 0 rows affected (0.17 sec)

mysql> insert into testup_myisam values (2) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into testup_myisam values (1) ;
Query OK, 1 row affected (0.00 sec)

mysql> update testup_myisam set a=a+1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> update testup_myisam set a=a-1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

-- REVERSE ORDER --

mysql> truncate table testup_myisam ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testup_myisam values (1) ;
Query OK, 1 row affected (0.00 sec)

mysql> insert into testup_myisam values (2) ;
Query OK, 1 row affected (0.00 sec)

mysql> update testup_myisam set a=a+1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> update testup_myisam set a=a-1 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

The problem for us is when we develop or migrate applications between
different databases.
By the way I think is not right that an update on the same set of rows will
be successful or failed if the rows are ordered or not, no?
I think it is something in correlation with visibility of rows in MVCC
(update=>insert + delete tuple).

What do you think about?

See you soon

Regards, Mat