insert ... select .. order by problem
insert ... select .. order by problem
am 02.09.2003 08:53:39 von Alejandro Paz
Hallo everyone !
I already sent this, but I think some people think is
not clear enough ;-)
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 I am using is :
INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE
h
I am doing an insert/select with order by, in both
cases I am using the same statemant.
When I use the same statement in my application (built
with C, and statically linked
to libmysqlclient.a) I get the reversed order.
When I test the statement in the mysql cli and I get
the results well sorted.
I checked the log and both statements are equal, (but
the two users used to access the DB are different,
the mysql cli user is root, and the other just have
enough permissions to select,
update,delete and insert in the tables).
I would like to know (if that is possible) what
happens.
Thanks in advance.
Ale
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
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
Re: insert ... select .. order by problem
am 02.09.2003 11:51:12 von Sergei Golubchik
Hi!
On Sep 01, Alejandro Paz wrote:
> Hallo everyone !
>
> I already sent this, but I think some people think is
> not clear enough ;-)
> And the statement I am using is :
>
> INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock WHERE
> h
>
> I am doing an insert/select with order by, in both
> cases I am using the same statemant.
>
> When I use the same statement in my application (built
> with C, and statically linked
> to libmysqlclient.a) I get the reversed order.
>
> When I test the statement in the mysql cli and I get
> the results well sorted.
What "results" do you mean here ?
INSERT command does not return any results.
Regards,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
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
Re: insert ... select .. order by problem
am 02.09.2003 11:59:06 von Alejandro Paz
Hi,
I use ORDER BY, because I want that order in PTemp
table, so I do not have to order them later (because
they are retrieved several times later).
1. Inserting with mysql c.l.i. :
I get the records well sorted : first by a, secondly
by b and finally by c (ascendig order). I'm using d to
relink both tables in a join.
select a,b,c from Stock,PTable where Stock.d=PTable.d;
2. Inserting with the application :
I get the records badly sorted : first by a, secondly
by b and finally by c, but in descending order. As all
records has the same value in a, so the records that
in case 1 start with '0' are at the beginning, the
same records here are at the end. I'm using d to
relink both tables in a join.
select a,b,c from Stock,PTable where Stock.d=PTable.d;
I thought that was clear when I said reverse order,
sorry.
--- Sergei Golubchik wrote:
> Hi!
>
> On Sep 01, Alejandro Paz wrote:
> > Hallo everyone !
> >
> > I already sent this, but I think some people think
> is
> > not clear enough ;-)
>
> > And the statement I am using is :
> >
> > INSERT INTO PTemp SELECT d,e,32,'E' FROM Stock
> WHERE
> > h
> >
> > I am doing an insert/select with order by, in both
> > cases I am using the same statemant.
> >
> > When I use the same statement in my application
> (built
> > with C, and statically linked
> > to libmysqlclient.a) I get the reversed order.
> >
> > When I test the statement in the mysql cli and I
> get
> > the results well sorted.
>
> What "results" do you mean here ?
> INSERT command does not return any results.
>
> Regards,
> Sergei
>
> --
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Sergei Golubchik
>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior
> Software Developer
> /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
> <___/ www.mysql.com
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
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
Re: insert ... select .. order by problem
am 02.09.2003 13:04:08 von Sinisa Milivojevic
Alejandro Paz writes:
> Hi,
>
> I use ORDER BY, because I want that order in PTemp
> table, so I do not have to order them later (because
> they are retrieved several times later).
>
> 1. Inserting with mysql c.l.i. :
>
> I get the records well sorted : first by a, secondly
> by b and finally by c (ascendig order). I'm using d to
> relink both tables in a join.
>
> select a,b,c from Stock,PTable where Stock.d=PTable.d;
>
> 2. Inserting with the application :
>
> I get the records badly sorted : first by a, secondly
> by b and finally by c, but in descending order. As all
> records has the same value in a, so the records that
> in case 1 start with '0' are at the beginning, the
> same records here are at the end. I'm using d to
> relink both tables in a join.
>
> select a,b,c from Stock,PTable where Stock.d=PTable.d;
>
> I thought that was clear when I said reverse order,
> sorry.
>
Hi!
The order of retrieval of rows does not necessary has to be the same
as the row order in the table.
You should get the same result with mysql cli and in application.
Try using ..store_result instead of ..use_result function.
Still, no order is guaranteed if your SELECT does not use GROUP BY or
ORDER BY.
Last but not least, if a,b,c are present in both tables, you should
specify a table in the select list.
--
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
Re: insert ... select .. order by problem
am 02.09.2003 15:37:34 von Sergei Golubchik
Hi!
On Sep 02, Alejandro Paz wrote:
> Hi,
>
> I use ORDER BY, because I want that order in PTemp
> table, so I do not have to order them later (because
> they are retrieved several times later).
>
> 1. Inserting with mysql c.l.i. :
>
> I get the records well sorted : first by a, secondly
> by b and finally by c (ascendig order). I'm using d to
> relink both tables in a join.
>
> select a,b,c from Stock,PTable where Stock.d=PTable.d;
>
> 2. Inserting with the application :
>
> I get the records badly sorted : first by a, secondly
> by b and finally by c, but in descending order. As all
> records has the same value in a, so the records that
> in case 1 start with '0' are at the beginning, the
> same records here are at the end. I'm using d to
> relink both tables in a join.
>
> select a,b,c from Stock,PTable where Stock.d=PTable.d;
Are you sure everything else is the same for both cases ?
Same mysqld version, same initial content for both tables, same settings
for mysqld variables ?
Did you execute both insert and select from mysql cli and your
application - or only insert from mysql cli and your application and
then select from mysql cli ?
(here, I use "insert" for your "INSERT ... SELECT ... ORDER BY" command,
and "select" for "SELECT ... FROM ... WHERE Stock.d=PTable.d" command")
Try to run - on the same server, with the same settings, same table
content - all the four combinations:
insert in mysql cli, select in mysql cli
insert in your app, select in mysql cli
insert in mysql cli, select in your app
insert in your app, select in your app
Don't forget to restore initial PTable content after each try.
Regards,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
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