Re: Union and Order By give strange results in Mysql 4.0.13
Neculai Macarie wrote: [] 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; [] 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; Making a twist to the samples provided select 'categ' as table_name, d_image, '.' as big from gallery_categ union select 'gallery' as table_name, d_image_small , d_image_big from gallery order by table_name; the results are amazing 'table_name','d_image','big' 'categ','img22.jpg','.' 'categ','img23.jpg','.' 'categ','img21.jpg','.' 'galle','img3.jpg','i' 'galle','img6.jpg','i' 'galle','img1.jpg','i' To my observations constants in a column declaration limit the column width to just fit the initial constant. Your choice of values 'gallary' and 'gallery-categ' just masked that out ;-) Yes, you are right. Based on your observation I was able to trick him with this query: select Trim( '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; mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Union and Order By give strange results in Mysql 4.0.13
Neculai Macarie wrote: To my observations constants in a column declaration limit the column width to just fit the initial constant. Your choice of values 'gallary' and 'gallery-categ' just masked that out ;-) Yes, you are right. Based on your observation I was able to trick him with this query: select Trim( '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; Well, it works for you, but preserving the original length don't feel logical. I fear this might change without notice. Come to think of select 'gallery ' as table_name, .. Yes, MySQL silently trims the trailing spaces for you!! HansH -- MySQL 4.1.0-alpha-max-nt on Windows XP using MySLQcc 0.9.2.B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
Re: Union and Order By give strange results in Mysql 4.0.13
Neculai Macarie wrote: [] 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; [] 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; Making a twist to the samples provided select 'categ' as table_name, d_image, '.' as big from gallery_categ union select 'gallery' as table_name, d_image_small , d_image_big from gallery order by table_name; the results are amazing 'table_name','d_image','big' 'categ','img22.jpg','.' 'categ','img23.jpg','.' 'categ','img21.jpg','.' 'galle','img3.jpg','i' 'galle','img6.jpg','i' 'galle','img1.jpg','i' To my observations constants in a column declaration limit the column width to just fit the initial constant. Your choice of values 'gallary' and 'gallery-categ' just masked that out ;-) A workaround can be found in creating an additional table -never to hold data- create table dummy ( _name char(25), _small char(100), _big char(100)); and selecting it upfront of the unions to the filled tables select * from dummy union select 'categ' , d_image, null from gallery_categ union select 'gallery' , d_image_small , d_image_big from gallery order by _name; BTW I use this kind of a dummy table just to easy (re)name collumns in a union-ed resultset: not a simngle 'as' in any select. HansH -- Running 4.1.0-alpha-max-nt On WindowsXP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
