Re: insert .. select with order by problem

Alejandro Paz writes:
> Hallo everyone !
> 
> Im using Mysql 4.0.12 on RedHat 7.3 x86
> I know it's not the last binary but I cannot upgrade
> now. (And i saw nothing about this in the changelog
> for 4.013 and 4.0.14)
> 
> I found the following : 
> 
> I have two tables :
> 
> Stock (InnoDB, primary key on d):
> 
> a char (16)
> b char (20)
> c char (20)
> d int
> e decimal (9,2)
> h int
> i int
> 
> PTemp (MyISAM, no keys):
> 
> d int
> e decimal
> f int
> g char (1)
> 
> And the statement is
> 
> insert into PTemp select d,e,32,'E' from Stock where
> h<i order by a,b,c
> 
> I test the statement in the mysql cli and I get the
> results well sorted.
> When I use the same statement in my application (built
> with C, and statically linked
> to libmysqlclient.a) I get the reversed order.
> 
> I checked the log and both statements are equal, (but
> the two users are different, 
> the mysql cli user is root, and the other just have
> enough permissions to select,
> update,delete ad insert in the tables).
> 
> I would like to know what happens.
> 
> Thanks in advance.
> 

HI!

I guess that you are not speaking about the INSERT command but most
probably about:

SELECT * FROM PTemp;

The order of the result set coming from SELECT statements without
ORDER BY / GROUP BY is not guaranteed.

It can be quite random. That is what ORDER BY is used for.

-- 

Sincerely,

-- 
For technical support contracts, go to https://order.mysql.com/?ref=msmi
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Mr. Sinisa Milivojevic <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB
/_/  /_/\_, /___/\___\_\___/   Fulltime Developer and Support Coordinator
       <___/   www.mysql.com   Larnaca, Cyprus


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]