Union and Order By give strange results in Mysql 4.0.13

[Neculai Macarie]

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