Bug #20487 INSERT INTO .... SELECT inserts in wrong order
Submitted: 15 Jun 2006 19:40 Modified: 16 Jun 2006 9:51
Reporter: Mike Christen Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22 OS:Mac OS X (OS-X)
Assigned to: CPU Architecture:Any

[15 Jun 2006 19:40] Mike Christen
Description:
Insert into table1 SELECT * from table2 scrambles the order of the insert if if you specify ORDER BY ID on table 2 ---

Insert from CSV does the same thing -- whether you specifiy record IDS or not, and let autoincrement assign ID's.

When you insert records from another source, you want to keep the order.

Thanks.

How to repeat:
Insert into table1 SELECT * from table2

Suggested fix:
insert records from table 2 into table 1 without scrambling the order.
[16 Jun 2006 9:51] Sveta Smirnova
I can't repeat it using las 5.0.23 sources
[25 Jun 2007 11:51] sukalyan chakraborty
use inline view to solv the problem.
in from clause use inline view which can be use order by caluse & 
can be inserted successfully.
[25 Jun 2007 11:55] sukalyan chakraborty
use like

INSERT INTO TBLPROTOCOLPROCEDURES
      (
      field1, field2, field3,
      field4, field5, field6, field7,field8
      )
      (
SELECT field1, field2, field3,
      field4, field5, field6, field7,field8
 FROM 
      (SELECT field1, field2, field3,
      field4, field5, field6, field7 ,field8
      ORDER BY field7);