Bug #16583 ALTER TABLE ADD ZEROFILL AUTO_INCREMENT does not fill in correct order

• Submitted: 17 Jan 2006 20:51
• Modified: 6 Feb 2006 11:05
• Reporter: Jonathan Miller
• Status: Not a Bug
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.6-alpha
• OS: Linux (Linux)
• Assigned to: Jonas Oreland
• CPU Architecture: Any

[17 Jan 2006 20:51] Jonathan Miller

Description:
rpl000005.test

*** r/rpl000005.result  2006-01-17 22:37:15.000000000 +0300
--- r/rpl000005.reject  2006-01-17 23:41:54.000000000 +0300
***************
*** 14,25 ****
  ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
  SELECT * FROM t1 ORDER BY id;
  name  age     id
! Andy  31      00000001
  Jacob 2       00000002
! Caleb 1       00000003
  SELECT * FROM t1 ORDER BY id;
  name  age     id
  Andy  31      00000001
! Jacob 2       00000002
! Caleb 1       00000003
  drop table t1;
--- 14,25 ----
  ALTER TABLE t1 ADD id int(8) ZEROFILL AUTO_INCREMENT PRIMARY KEY;
  SELECT * FROM t1 ORDER BY id;
  name  age     id
! Caleb 1       00000001
  Jacob 2       00000002
! Andy  31      00000003
  SELECT * FROM t1 ORDER BY id;
  name  age     id
  Andy  31      00000001
! Caleb 1       00000002
! Jacob 2       00000003
  drop table t1;

How to repeat:
./mysql-test-run  --mysqld=--binlog-format=row  --with-ndbcluster-all --mysqld=--default-storage-engine=ndb --force --with-ndbcluster-slave --do-test=rpl000005 --ndb-extra-test

[6 Feb 2006 11:05] Jonas Oreland

It is to unspecified which order an alter table will modify rows.
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

This is basically the same as doing "select * from table" and expect the rows to come
  in a certain order. Wo/ "order by" the order is unspecified.

And even if mysql has lots of different add on (unstandard syntax/features) there is no one specifying an order for alter table.

Therefor this is a bug in the testcase.
I suggest changing "order by id" to "order by name"