Posted by Marek Kilimajer on 01/14/05 17:38

adwin wijaya wrote:
> Hi...
>
>
> I have an query for mysql that looks like:
>
> SELECT "group" as type FROM mytable WHERE id ="101010"
> UNION
> SELECT "individual" as type FROM myothertable WHERE id="101010"
>
> The strange result if only one result displayed from myothertable, so
> the "type" will become "indiv" instead of individual.
>
> But when I tried to switch the query become :
>
> SELECT "individual" as type FROM myothertable WHERE id="101010"
> UNION
> SELECT "group" as type FROM mytable WHERE id ="101010"
>
> it could displaye the result correctly. I dont know why .. is this mysql
> bug ?
>
>

http://dev.mysql.com/doc/mysql/en/UNION.html

The types and lengths of the columns in the result set of a UNION take
into account the values retrieved by all the SELECT statements. 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:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| b |
+---------------+

That limitation has been removed as of MySQL 4.1.1:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a |
| bbbbbbbbbb |
+---------------+

[Back to original message]