column width in union

[mikeg]

Hi

Column width in a series of UNIONs is determined by the width of the
first SELECT. Here is an example:

mysql> ( SELECT "foo" ) UNION ( SELECT "fnord" );
+-----+
| foo |
+-----+
| foo |
| fno |
+-----+
2 rows in set (0.00 sec)

This is in MySQL 4.0.21.

In my real queries, I cannot know ahead of time the maximum length of
any column as returned by any of the SELECT blocks. My workaround is
to start my UNION chain with a

SELECT "xxxxxxxxxxxxxxxx",
"xxxxxxxxxxxxxxxx",
...

And then in the code that uses the results, ignore any rows that have
"xxxxxxxxxxxxxxxx" in them. This is extremely kludgy.

There was as thread from 2003-08-24 with the subject "Union and Order
By give strange results in Mysql 4.0.13" which describes this problem.
It was unresolved (only workarounded) over a year ago.

Does anyone know of a real fix?

-Michal

[Bill Karwin]

mikeg wrote:

Column width in a series of UNIONs is determined by the width of the
first SELECT. ... Does anyone know of a real fix?

I thought of using CAST(foo as CHAR(16)), but that seems to be an error.

How about using a temporary table?

CREATE TEMPORARY TABLE unionResult ( foo CHAR(16 );
INSERT INTO unionResult SELECT "foo";
INSERT INTO unionResult SELECT "fnord";
INSERT INTO unionResult SELECT "12345678901234567";
SELECT foo FROM unionResult;
+------------------+
| foo |
+------------------+
| foo |
| fnord |
| 1234567890123456 |
+------------------+

Regards,
Bill K.