MySQL Bugs: #81491: delete then insert multiple values produces data in wrong order.

Bug #81491	delete then insert multiple values produces data in wrong order.
Submitted:	18 May 2016 15:05	Modified:	18 May 2016 18:13
Reporter:	tom mlay	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.49-c11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert data wrong order

Description:
delete then insert multiple values produces data in wrong order. The behavior is different between 5.5.6 on windows (correct) and 5.5.49-c11 on Linux/Centos (incorrect).

For a variety of reasons, I delete a set of records from a table and then insert new ones. As a minimal demonstration see the sql commands below.

I create a table.
Insert multiple values.
Select shows the values in the proper order (id's asc).
Delete those values.
Insert the same values.
Select shows the values in the wrong order (new id's DESC).

It appears mysql is getting the correct id with the correct set of values but I would epxect the data to be written in the order the values are provided.

The work-around is to select with "order by id" but I fear this may be a sign of some larger problem.

When I execute these commands under Windows wampp (mysql 5.5.6) I get the results I expect.

This occurs in both the command line and through php.

How to repeat:
drop table if exists x1;
create table x1 (
	id			int AUTO_INCREMENT,
	report		int default 0,
	file_id		int default 0,
	field_id	int default 0,
	PRIMARY KEY(id)
);

insert into x1 (report,file_id,field_id) values(99,4,69),(99,4,86),
(99,4,66),(99,4,67),(99,4,68),(99,4,70),(99,4,71),(99,4,72),
(99,4,73),(99,4,74),(99,4,75),(99,4,76),(99,4,77),(99,4,78),
(99,4,79),(99,4,80),(99,4,81),(99,4,82),(99,4,83),(99,4,84),
(99,4,85),(99,4,87),(99,4,88),(99,4,89);

select * from x1;

delete from x1 where report = 99;

insert into x1 (report,file_id,field_id) values(99,4,69),(99,4,86),
(99,4,66),(99,4,67),(99,4,68),(99,4,70),(99,4,71),(99,4,72),
(99,4,73),(99,4,74),(99,4,75),(99,4,76),(99,4,77),(99,4,78),
(99,4,79),(99,4,80),(99,4,81),(99,4,82),(99,4,83),(99,4,84),
(99,4,85),(99,4,87),(99,4,88),(99,4,89);

select * from x1;

Thank you for your bug report. However, it is not a bug ...

One of the basic premises of the relational model is that data sets do not have an ordering. It thus applies to all SQL databases. It was stipulated first time by the great C.J.Date, who was co-inventor of SQL.

Hence, when the language was first time standardized, that was clearly stipulated. Hence, on page 373 of the SQL-92 standard it writes loud and clear: "If an <order by clause> is not specified, then the table specified by the <cursor specification> is T and the ordering of rows in T is implementation-dependent."

Hence, no guarantee ....

Thanks for your quick reply.

I understand there is no intrinsic ordering of data in an sql table. However, in every previous version of mysql and in every other sql product I have worked with (MS Sql, IBN/SQL,Postgress) I have been able to rely on the data being written in the order in which I insert it, i.e. the select * from table returns the records in the order inserted.

Your interpretation leads to two significant issues:
1) If I insert 10 records in one insert command I get a different result than if I do 10 insert commands.
2) I now have to do an order by id in order to retrieve the data in the order I inserted it. This will be a MAJOR performance issue on large data sets.

Thanks,
-Tom Mlay