MySQL Bugs: #1709: DELETE and subsequent INSERTs result in out of order rows

Bug #1709	DELETE and subsequent INSERTs result in out of order rows
Submitted:	30 Oct 2003 4:13	Modified:	30 Oct 2003 8:22
Reporter:	Bob Terrell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.16	OS:	MacOS (Mac OS X 10.2.8)
Assigned to:		CPU Architecture:	Any

Description:
When using a table in which one column (the primary key) is auto_increment, running a DELETE 
query deleting some non-zero number of rows, then inserting additional rows, results in the newly 
inserted rows being entered in the table in reverse of the deleted rows before adding additional 
rows to the end of the table.

Removing all columns from the table other than the auto_increment field result in the table being 
displayed in the expected order. Adding columns to the table after doing so restores the table to 
its "wrong" order.

How to repeat:
The MySQL statements

CREATE TABLE `d` (
  `id` tinyint(3) unsigned NOT NULL auto_increment,
  `text` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
DELETE FROM d WHERE id>=2 AND id<=4;
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
INSERT INTO d (text) VALUES ('abcde');
SELECT * FROM d;

will result in a table with the id's in the following order: 
1
10
9
8
5
6
7
11
12
13

Suggested fix:
A workaround is to run the query with ORDER BY id. As for fixes, sorry; I'm not familiar with the 
source.

Also occurs with MySQL 4.0.15-nt. I'm pretty sure this didn't happen several versions ago.

Tables do not normally have an "order", per se, so generally if you need rows to be returned in a specific order you should be using an ORDER BY clause in your SELECT statement.

InnoDB clustered indexes and using ALTER TABLE tablename ORDER BY columnname with MyISAM do impact the physical ordering but, basically, the reported behaviour is perfectly normal.

Thank you

Sorry; I was just pretty sure that it used to work the expected way. Unless there's a reason it can't, 
perhaps you can consider this a feature request. One would expect the order of a table to reflect 
the order of insert statements.

I'm having the same problem.  I agree with Bob Terrell.  I expect the rows to be physically inserted in the same order as I submitted.

When I do update to the rows in my table, I DELETE all the rows that need to be updated, then insert them back with new values ONE by ONE.  Users expecte to see the results shown in the same order as they entered, but the inserted rows out of order PHYSICALLY.  Some are in reversed order and some are in correct order.  So frustrated!!!

What I want is the rows to be inserted into the table physically in the same order as my INSERT query.  I did the INSERT query one row each time.  I don't understand why MySQL not following my query instruction.

MySQL 3.23.58
Using PHP script to do the INSERT and DELETE