By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,273 Members | 1,037 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,273 IT Pros & Developers. It's quick & easy.

Union and Order By give strange results in Mysql 4.0.13

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.