Bug #20487 INSERT INTO .... SELECT inserts in wrong order

• Submitted: 15 Jun 2006 19:40
• Modified: 16 Jun 2006 9:51
• Reporter: Mike Christen
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.22
• OS: MacOS (OS-X)
• 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);