insert .. select with order by problem

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 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
/ /|_/ / // /\ \/ /_/ / /__ 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/bugs?unsub=gcdmb-bugs@m.gmane.org