From http://dev.mysql.com/doc/mysql/en/union.html :
Before MySQL 4.1.1, a limitation of UNION is that only the values
from the first SELECT are used to determine result column types and
lengths. This could result in value truncation if, for example, the
first SELECT retrieves shorter values than the second SELECT:
You can either update to version 4.1.1 or later, when the problem was
solved, or make sure that the first SELECT retrieves columns that are
wide enough and character types.
On 8/5/05, Kapoor, Nishikant [EMAIL PROTECTED] wrote:
I have a UNION whose statements when interchanged gives a different result. I
can understand the change in the order of the rows, but how is it that
'picture' gets the correct value in (a) but not in (b)? Here are the two
queries and their results:
(a)
(SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE
te.person_id = u.uId AND te.tId IN (1) LIMIT 5)
UNION
(SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE
te.person_id = a.person_id AND te.tId IN (1) LIMIT 5);
last_name picture
Kapoor avataar02.png from table u
Manni 0 from table u
Office of Technology Assessment 0 from table a
Queue Readers 0 from table a
Milnes 0 from table a
(b)
(SELECT a.last_name, 0 picture FROM teamEntry te, author a WHERE
te.person_id = a.person_id AND te.tId IN (1) LIMIT 5)
UNION
(SELECT u.lName last_name, u.picture FROM teamEntry te, user u WHERE
te.person_id = u.uId AND te.tId IN (1) LIMIT 5);
last_name picture
Office of Technology Assessment 0 from table a
Queue Readers 0 from table a
Milnes 0 from table a
Kapoor 0 from table u
Manni 0 from table u
Thanks for your help,
Nishi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
