Re: [suspicious - maybe spam] [PHP] [suspicious - maybe spam] strange in MySQL Query.

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    |

+---------------+