469,903 Members | 1,813 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

Union and Order By give strange results in Mysql 4.0.13

Hi!

Using Union and Order By gives strange behaviour in the following test-case:

drop table if exists gallery;
drop table if exists gallery_categ;
# create test tables
create table gallery (d_image_small char(100), d_image_big char(100));
create table gallery_categ (d_image char(100));
# insert test data
insert into gallery(d_image_small, d_image_big) values('img1.jpg',
'img2.jpg');
insert into gallery(d_image_small, d_image_big) values("img3.jpg",
"img4.jpg");
insert into gallery(d_image_small, d_image_big) values("img6.jpg",
"img5.jpg");
insert into gallery_categ(d_image) values("img21.jpg");
insert into gallery_categ(d_image) values("img22.jpg");
insert into gallery_categ(d_image) values("img23.jpg");
This query
select 'gallery' as table_name, d_image_small, d_image_big
from gallery
UNION
select 'gallery_categ' as table_name, d_image, NULL
from gallery_categ
order by table_name;

returns this:
+------------+---------------+-------------+
| table_name | d_image_small | d_image_big |
+------------+---------------+-------------+
| gallery | img21.jpg | NULL |
| gallery | img6.jpg | img5.jpg |
| gallery | img3.jpg | img4.jpg |
| gallery | img1.jpg | img2.jpg |
| gallery | img23.jpg | NULL |
| gallery | img22.jpg | NULL |
+------------+---------------+-------------+

Which is wrong, because the table_name field has the same value for both
tables.
But the following query works:
select 'gallery' as table_name, d_image_small, d_image_big
from gallery
union
select 'categ' as table_name, d_image, NULL
from gallery_categ
order by table_name;

+------------+---------------+-------------+
| table_name | d_image_small | d_image_big |
+------------+---------------+-------------+
| categ | img21.jpg | NULL |
| categ | img23.jpg | NULL |
| categ | img22.jpg | NULL |
| gallery | img6.jpg | img5.jpg |
| gallery | img3.jpg | img4.jpg |
| gallery | img1.jpg | img2.jpg |
+------------+---------------+-------------+

<mack />
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 1976

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by Xenophobe | last post: by
reply views Thread by Marek Lewczyk | last post: by
reply views Thread by unixman | last post: by
1 post views Thread by mikeg | last post: by
3 posts views Thread by Matias Silva | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.