insert ... select .. order by, problem

[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
hi ORDER BY a,b,c;

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 (the
records that start with '0' are at the end).

When I test the statement in the mysql cli and I get
the results well sorted (the records that start with
'0' are at the begining).

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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert ... select .. order by, problem

[Alejandro Paz]

Hi Stephan,

Let's see the case :

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.

2. Inserting with the application :

I get the records well 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.

I thought that was clear when I said reverse order,
sorry.

So you see what I mean ?

thanks !

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Bug Report

[Alejandro Paz]

 
 

__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Bug Report, timestamp columns

[Alejandro Paz]

Hi Mysql,

This is a bug report.

There are two cases, because the bug is quite old, I
detected it the first time
a year ago on a 3.23.44 with MyISAM, but I thought it
would be fixed soon, 
sorry.

This bug happens in 4.0.8 too with MyISAM tables.

Test Case 1
---
Operating System: Linux Intel, SuSE 6.3 (Kernel
2.2.13) 
Processor  : ADM K6-2 400 
System RAM : 128M
Disk Subsystem : SCSI-2 (dpt)
FIle Sytem handler : ext2

MySQL version: mysql-max-3.23.49a-linux-gnu-i686
Table Handler: Derkeley DB.

Symptom : Two colums are updated insted of one.

---the script starts here

create database prueba;
use prueba;
create table pr (a int, b timestamp, c timestamp, d
timestamp, e int) type=bdb;
insert into pr values (0, now(), now(), now(), 1);
select * from pr;
#
# Insert a delay here !!!
#
update pr set c=now();
select * from pr;

the script ends here-

the output from `mysql -t pr1.sqlsal' client is:

+--++++--+
| a| b  | c  | d  
   | e|
+--++++--+
|0 | 20030324145209 | 20030324145209 |
20030324145209 |1 |
+--++++--+
+--++++--+
| a| b  | c  | d  
   | e|
+--++++--+
|0 | 20030324145304 | 2003010110 |
20030324145209 |1 |
+--++++--+

As you can see the colum `b' is updated, too.
Note, you have to insert a delay of almost 1 second
between the first select
and the update, because the column `b' takes the
current time!.

Only happens with timestamp columns not with datetime
ones.

Test Case 2
---
Operating System: Linux Intel, RedHat 7.3 6.3 (Kernel
2.4.18) 
Processor  : ADM K6-2 400 
System RAM : 128M
Disk Subsystem : SCSI-2 (dpt)
FIle Sytem handler : ext2

MySQL version: mysql-standard-4.0.12-pc-linux-i686
Table Handler: InnoDB.

Symptom : Two colums are updated insted of one.

---the script starts here

create database prueba2;
use prueba2;
create table pr (a int, b timestamp, c timestamp, d
timestamp, e int) type=innodb;
insert into pr values (0, now(), now(), now(), 1);
select * from pr;
#
# Insert a delay here !!!
#
update pr set c=now();
select * from pr;

the script ends here-

the output from `mysql -t pr2.sqlsal' client is:

+--++++--+
| a| b  | c  | d  
   | e|
+--++++--+
|0 | 20030324145653 | 20030324145653 |
20030324145653 |1 |
+--++++--+
+--++++--+
| a| b  | c  | d  
   | e|
+--++++--+
|0 | 20030324145739 | 2003010111 |
20030324145653 |1 |
+--++++--+

As you can see the colum `b' is updated, too.
Note, you have to insert a delay of almost 1 second
between the first select
and the update, because the column `b' takes the
current time!.

Only happens with timestamp columns not with datetime
ones.

I hope it will help you to make MySQL better.


For further information, please don't hesitate to
contact me at:

[EMAIL PROTECTED]

Roebrto Alejandro Paz Schmidt
Republica Argentina



__
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]